Admin: /* BadServiceStore */

2021-12-16T17:21:32Z

BadServiceStore

← Older revision		Revision as of 17:21, 16 December 2021
Line 478:		Line 478:
	AND object_name = 'SQLServer:Databases'		AND object_name = 'SQLServer:Databases'
	AND instance_name = 'BADServiceStore'		AND instance_name = 'BADServiceStore'

			=Create new user over an existing database user=
			use database;
			alter user <user> with login=<user>

			=Create Identity for Azure Managed Instance=
			CREATE CREDENTIAL [https://SANAME.blob.core.windows.net/CONTAINER]
			WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
			, SECRET = 'sp=racwdl.......'
			*Drop
			drop CREDENTIAL [https://stcoldabn01.blob.core.windows.net/cold-abn-data]

Admin: Created page with "this is about sql statements which we have collected by the time.

=Useful links= Glenn Berry's SQL Server Diagnostic Scripts *https://www.sqlskills.com/blog..."

2020-11-27T18:16:29Z

Created page with "this is about sql statements which we have collected by the time. =Useful links= Glenn Berry's SQL Server Diagnostic Scripts *https://www.sqlskills.com/blog..."

New page

this is about sql statements which we have collected by the time.
 

=Useful links=
Glenn Berry's SQL Server Diagnostic Scripts
*https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-august-2018/
Or the local copy here: http://wiki.intern/index.php/Glenn_Berry%27s_SQL_Server_Diagnostic_Scripts

=Top 10 Bookings =
SELECT count(*) as exp1,bookingdate FROM tblIBE_LiveBookings_Basic where test=0 and bookingdate >= 20151216 and bookingdate <= 20170117 group by bookingdate order by exp1 desc

=Add mySQL User=
mysql> GRANT ALL PRIVILEGES ON mylvs.* TO mylvsuser@localhost IDENTIFIED BY 'demo';

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'xxxxxxx';

'''HOT'''
GRANT ALL PRIVILEGES ON vfAutomation.* TO 'username'@'%' IDENTIFIED BY 'xxxxx';

=Remove mySQL User=
REVOKE ALL PRIVILEGES ON vfAutomation.* FROM 'azoeller '@'%';
DROP USER 'azoeller'@'%';

=List user=
SELECT User FROM mysql.user;
SHOW GRANTS FOR 'user'@'host';

=Change User pwd =
SET PASSWORD FOR 'user'@'%' = PASSWORD('password');

=Dump mySQL Table only=
mysqldump -u root -p Statistic tblAirports > mysql_tblAirports.sql

=Grant a machine name to security=
CREATE LOGIN ['''MyDomain\MyMachineName$'''] FROM WINDOWS

=List / kill user=
SELECT SPID,STATUS, PROGRAM_NAME, LOGINAME=RTRIM(LOGINAME),HOSTNAME,CMD
FROM MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = ''''MyDatabaseName'''' AND DBID != 0

'''Note'''
You might need to set the permission for this too:
GRANT VIEW ANY DEFINITION TO [fra01\misadmin];
GRANT VIEW SERVER STATE TO [fra01\misadmin];

To kill connections you would set:
GRANT ALTER ANY CONNECTION TO [fra01\misadmin];

Tipp:
kill spid

=Close User Access=

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER

=List costs=
*This may work on '''MS SQL > 2005''' only
ROUND(s.avg_total_user_cost *
s.avg_user_impact
* (s.user_seeks + s.user_scans),0)
AS [Total Cost]
,d.[statement] AS [Table Name]
,equality_columns
,inequality_columns
,included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC

=Move temp table=
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ''''c:\MyDir\tempdb.mdf'''');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ''''c:\MyDir\templog.ldf'''');
GO

=Count data on a particiular hour per date=
Select
sum(case when FlightType = 'WFE' then 1 else 0 end) WFECount,
sum(case when FlightType = 'NET' then 1 else 0 end) NETCount,
sum(case when FlightType = 'PUB' then 1 else 0 end) PubCount,
BookingDate
From tblIBE_LiveBookings_Basic
group by BookingDate

=Formular condition within select statement=
use CountingService
go
DECLARE @sDate DATE='2013-03-16' DECLARE @eDate DATE='2013-04-16'
SELECT Count(*) as exp1, '''case when pcc = '' then user else pcc end''', source
FROM Bookings
Where
convert(date, BookingDateTime) >= @sDate AND convert(date, BookingDateTime) <= @eDate
group by PCC,Source order by exp1 desc

=Agent Job - delete by Date=
DELETE FROM tblFlightAPIStoredFlight WHERE (FlightDateTime < GETDATE() - 1)

=Bulk insert with T-SQL=
*Insert bulk.sql into table tblVia

BULK INSERT tblVia
FROM 'u:\perl\webfares\Condor\bulk.sql'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR ='|\n'
);

=Bulk permissions=

GRANT ADMINISTER BULK OPERATIONS TO [intern\misadmin]

=Insert & Join=

*MS
UPDATE tblIBE_LiveBookings_Basic
SET Test = 0
FROM tblIBE_LiveBookings_Basic INNER JOIN
tblIBE_LiveBookings_Passenger ON tblIBE_LiveBookings_Basic.ID = tblIBE_LiveBookings_Passenger.UniqueID
WHERE (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hhiker%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hhiker%') AND
(tblIBE_LiveBookings_Basic.Test = 1) AND (tblIBE_LiveBookings_Basic.BookingDate LIKE '201301%') AND
(tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'test%') AND
(tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'test%') AND
(tblIBE_LiveBookings_Basic.Filekey <> 'TESTKEY') and (tblIBE_LiveBookings_Basic.Filekey <> 'TESTBOOKING')

*MySQL
UPDATE tblIBE_LiveBookings_Basic
left JOIN tblIBE_LiveBookings_Passenger ON tblIBE_LiveBookings_Basic.ID = tblIBE_LiveBookings_Passenger.UniqueID
SET Test = 0
WHERE (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hhiker%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hhiker%') AND
(tblIBE_LiveBookings_Basic.Test = 1) AND (tblIBE_LiveBookings_Basic.BookingDate LIKE '201306%') AND
(tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.FirstName NOT LIKE 'test%') AND
(tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'hitch%') AND (tblIBE_LiveBookings_Passenger.LastName NOT LIKE 'test%') AND
(tblIBE_LiveBookings_Basic.Filekey <> 'TESTKEY') and (tblIBE_LiveBookings_Basic.Filekey <> 'TESTBOOKING');

*Check this for join, left join, right join and outer join:
http://joins.spathon.com/

=MySQL Add and Del User=

CREATE USER 'admin'@'%' IDENTIFIED BY 'xxxxx';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
DROP USER 'admin'@'%';

=MySQL Create user by script=

#!/bin/bash
DATABASE="bla"
USERNAME="bla"
PASSWORD="bla"
Q1="create database if not exists $DATABASE;"
Q2="grant usage on $DATABASE.* to $USERNAME@localhost identified by '$PASSWORD';"
Q3="grant all privileges on $DATABASE.* to $USERNAME@localhost;"
SQL="${Q1}${Q2}${Q3}"
mysql -u root -p -e "$SQL"

=MS SQL restore Database to a new DataBase Name=

RESTORE DATABASE FlightAPI_DER FROM DISK='d:\FlightAPI.bak'
WITH
MOVE 'FlightAPI' TO 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FlightAPI_DER.mdf',
MOVE 'FlightAPI_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FlightAPI_DER_log.ldf'

=Grant execute to stored procedures=
use vfEntry_HLKDB1;
GRANT EXEC TO [production\ibeadmin]

=Grant select to table only (MS)=
1: Create the user and map '''public''' permission to the table, then:
GRANT SELECT ON "dbo"."tblFlightAPIErrorDefinitions" TO "fapireader";

=Set multi user=
-- Start in master
USE MASTER;
ALTER DATABASE [vfEntry_HLKDB1] SET MULTI_USER

=View user permission=
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

=Delete data older then n days=
DELETE FROM tblBackOfficeFileTransferLog
WHERE (Created < DATEADD(day, - 90, GETDATE()))

=Restore database which is in use=

use master;
ALTER DATABASE FlightAPIProd SET SINGLE_USER WITH ROLLBACK IMMEDIATE

use master;
RESTORE DATABASE FlightAPIProd FROM DISK = 'C:\fapi.prod.bak' WITH REPLACE

use master;
ALTER DATABASE FlightAPIProd SET MULTI_USER

=Highest CPU Rate=

-- Find queries that have the highest average CPU usage
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC

*Another:
-- Find queries that take the most CPU overall
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC

=Show disk space by table=
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
totalspacekb

=Join for FAPI User and PCC=
*Use this query to show a pcc/user summary (without passwords) and save your time to answer annoying question
SELECT tblFlightAPILogin.UserName, tblFlightAPIPCC.GUID, tblFlightAPIPCC.PCC, tblFlightAPIPCC.TerminalCountry, tblFlightAPIPCC.GDSName, tblFlightAPIPCC.PCCUser
FROM tblFlightAPIPCC INNER JOIN
tblFlightAPILogin ON tblFlightAPIPCC.GUID = tblFlightAPILogin.UserGuid AND (tblFlightAPIPCC.GDSName = 'F1' OR tblFlightAPIPCC.GDSName = 'T1')

*Use this query to show all pcc/user details
SELECT tblFlightAPILogin.UserName, tblFlightAPIPCC.GUID, tblFlightAPIPCC.PCC, tblFlightAPIPCC.IsDefault, tblFlightAPIPCC.TerminalCountry, tblFlightAPIPCC.Password, tblFlightAPIPCC.GDSName,
tblFlightAPIPCC.PCCUser, tblFlightAPIPCC.ReferenceID, tblFlightAPIPCC.OrganisationID, tblFlightAPIPCC.PCCID, tblFlightAPIPCC.AccessPoint
FROM tblFlightAPIPCC INNER JOIN
tblFlightAPILogin ON tblFlightAPIPCC.GUID = tblFlightAPILogin.UserGuid AND (tblFlightAPIPCC.GDSName = 'F1' OR
tblFlightAPIPCC.GDSName = 'T1')

=Rebuild all indexes=

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

=Reduce Records in FAPI DB=
Use the following statement to delete flight data following foreign keys
DELETE FROM tblFlightAPIStoredFlight WHERE (FlightDateTime < GETDATE() - 7)

=Transfer Database to a another SQL and create users from there=
Problem: We need to transfer the PathFinder DB into the new Velia network and then create and assign local domain users
*1 Backup
Hint: Make sure you delete the provious backup to avoid FILE assignments
backup database PathFinderDB2 to disk='e:\backup\PathFinderDB2.bak'
*2 Restore
use Master;
RESTORE DATABASE PathFinderDB2
FROM DISK = 'C:\PathFinderDB2.bak'
WITH MOVE 'PathFinderDB2' TO 'C:\Data\PathFinderDB2.mdf',
MOVE 'PathFinderDB2_Log' TO 'C:\Data\PathFinderDB2.ldf',
FILE=1,REPLACE;
*3: Assign local domain users to the new restored Database
use PathFinderDB2;
CREATE USER [FRA01\hhservice] FOR LOGIN [FRA01\hhservice]
EXEC sp_addrolemember 'db_datareader', 'FRA01\hhservice';
EXEC sp_addrolemember 'db_datawriter', 'FRA01\hhservice';
EXEC sp_addrolemember 'db_executestoredprocedures', 'FRA01\hhservice';

=Create and assign user in ms sql=
use PathFinderDB2;
CREATE USER [FRA01\hhservice] FOR LOGIN [FRA01\hhservice]
EXEC sp_addrolemember 'db_datareader', 'FRA01\hhservice';

=Grant permission to a single table only=
*Assign Public permissions to the database
Optional:
Use Statistic
exec sp_msforeachtable "DENY SELECT ON '?' TO [intern\informationservice];"
Then:
GRANT SELECT ON tblAirports to [intern\informationservice]

=Change Logical File Name (MSSQL)=
*Get the current name first, sample db = AlomaClient_AvorisNortravel

USE AlomaClient_AvorisNortravel
GO
SELECT file_id, name as [logical_file_name],physical_name
from sys.database_files

Return

1 AlomaClient1_New_Data C:\Data\AlomaClient_AvorisNortravel.mdf
2 AlomaClient1_New_Log C:\Data\AlomaClient_AvorisNortravel_1.ldf

*Change the name by:
USE [master];
GO
ALTER DATABASE [AlomaClient_AvorisNortravel] MODIFY FILE ( NAME = AlomaClient1_New_Data, NEWNAME = AlomaClient_AvorisNortravel_Data );
GO

And

USE [master];
GO
ALTER DATABASE [AlomaClient_AvorisNortravel] MODIFY FILE ( NAME = AlomaClient1_New_Log, NEWNAME = AlomaClient_AvorisNortravel_Log );
GO

=Most I/O intensive query the last 6 hours=
*Note: The query can take a long time to complete
SELECT TOP (20)last_execution_time, total_logical_reads/execution_count AS [avg_logical_reads],
total_logical_writes/execution_count AS [avg_logical_writes],
total_worker_time/execution_count AS [avg_cpu_cost], execution_count,
total_worker_time, total_logical_reads, total_logical_writes,
(SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
(SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE statement_end_offset
END - statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
last_logical_reads, min_logical_reads, max_logical_reads,
last_logical_writes, min_logical_writes, max_logical_writes,
total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
(total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND (total_worker_time/execution_count) > 100 AND last_execution_time>DATEADD(hh,-6,GETDATE())
ORDER BY io_weighting DESC;

=Transaction per second=
==TransactionCache==
DECLARE @cntr_value bigint
SELECT @cntr_value = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND object_name = 'SQLServer:Databases'
AND instance_name = 'TransactionCache'
WAITFOR DELAY '00:00:01'
SELECT cntr_value - @cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND object_name = 'SQLServer:Databases'
AND instance_name = 'TransactionCache'
==BadServiceStore==
DECLARE @cntr_value bigint
SELECT @cntr_value = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND object_name = 'SQLServer:Databases'
AND instance_name = 'BADServiceStore'
WAITFOR DELAY '00:00:01'
SELECT cntr_value - @cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND object_name = 'SQLServer:Databases'
AND instance_name = 'BADServiceStore'

SQL Cheat Sheet - Revision history

Admin: /* BadServiceStore */

Admin: Created page with "this is about sql statements which we have collected by the time. =Useful links= Glenn Berry's SQL Server Diagnostic Scripts *https://www.sqlskills.com/blog..."

Admin: Created page with "this is about sql statements which we have collected by the time.

=Useful links= Glenn Berry's SQL Server Diagnostic Scripts *https://www.sqlskills.com/blog..."