SQL Queries

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Blocking Chain


Blocking Chain

Identify blocking chain processes and head of the blocking chain with wait types.

StoredProcs


Execution Counts
Query to gather stored procedure execution count , average and max elapsed time , reads, writes

SPID-SQLText

RowCounts

RenameDB

Kill SPIDS to Obtain Exclusive Database Lock

ClusterInfo

Backup-Restore

Dynamic Restore from Backup Flie
http://ramblingsofraju.com/sql-server/dynamically-restore-sql-server-database-from-backup/

— Identify Restored Database Source and TimeStamp

xp_cmdshell

Security

Script to Create a Login  ,  Create Database User from the Login
and Granting SELECT,UPDATE,INSERT,DELETE,EXEC Permissions

Enable successful and Failed Logins

Query Error Log for Successful and Failed Logins

Enumerate User Permissions

Enumerate Database Owners and Change it to “SA”

Indexes

Useful queries for identifying least and most used indexes , quick way rebuild all indexes , gather index name and size .

Least Used 20 Indexes

Results

ServerNameDatabaseNameObjectNameIndexIDSeeksScansLookupsUpdatesRows
DevSQLServerSampleDBFileValidationError63007717928
DevSQLServerSampleDBFileValidationError811107717928
DevSQLServerSampleDBFileValidationError711507717928

Top 20 Most Fragmented Objects

Results:

ServerNameDatabaseNameTableNameIndexNameIndexTypeavg_fragmentation_in_percent
DEVSQLServerJirauserhistoryitemuh_type_user_entityNONCLUSTERED INDEX98.0582524271845
DEVSQLServerJirachangegroupchggroup_issueNONCLUSTERED INDEX96.6207759699625
DEVSQLServerJiracwd_user_attributesidx_user_attr_dir_name_lvalNONCLUSTERED INDEX96.4285714285714
DEVSQLServerJiraAO_7DEABF_SCHEDULEpk_AO_7DEABF_SCHEDULE_IDCLUSTERED INDEX96.2962962962963
DEVSQLServerJirajiraissueissue_assigneeNONCLUSTERED INDEX95.7894736842105
DEVSQLServerJiraAO_575BF5_PROVIDER_ISSUEindex_ao_575bf5_pro741170824NONCLUSTERED INDEX95.4545454545455
DEVSQLServerJiraremembermetokenremembermetoken_username_indexNONCLUSTERED INDEX95
DEVSQLServerJiraAO_7DEABF_TESTSTEPpk_AO_7DEABF_TESTSTEP_IDCLUSTERED INDEX95
DEVSQLServerJiracwd_user_attributesPK_cwd_user_attributesCLUSTERED INDEX94.7368421052632
DEVSQLServerJiraAO_7DEABF_CHANGE_ZJEITEMpk_AO_7DEABF_CHANGE_ZJEITEM_IDCLUSTERED INDEX94.6843853820598

Missing Indexes

Results

ServerNameDatabaseNameTableNameuser_seeksuser_scansequality_columnsincluded_columnsAvg_Estimated_ImpactLast_User_Seek
DEVSQLServerJirapropertyentry344160[ENTITY_NAME], [ENTITY_ID]NULL3158356.322016-08-03 19:36:46.173
DEVSQLServerJirajiraaction60[AUTHOR][issueid], [CREATED]4352016-08-03 14:05:17.617
DEVSQLServerJirajiraaction30[actiontype]NULL181.742016-08-02 09:36:57.623
DEVSQLServerJirauserhistoryitem20NULL[ID]186.82016-08-03 14:05:57.240
DEVSQLServerJirachangegroup10[AUTHOR][issueid], [CREATED]50.592016-08-03 10:50:46.970

Percentiles

Percentile Calculation
This comes very handy to calculate percentiles for stored procedure Duration which are captured in Trace Files.

StoredProcName25th Percentile50th Percentile75th Percentile100th Percentile
exec [dbo].[ProcA]6256.25824612213.755799489
exec [dbo].[ProcB]33267.2557639.5154683.25163191464
exec [dbo].[ProcC]6059.758376140275796761
exec [dbo].[ProcD]379647311146719218

Misc

Miscellaneous queries 

Maintenance

Available Drive Space

Results:

ServerNameDriveLetterSpaceFreeinMB
DevSQLServerG2108673
DevSQLServerL694004
DevSQLServerT452866

Virtual File Stats:
Returns I/O statistics for database files, including log files
Function Usuage : fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } ) 

https://msdn.microsoft.com/en-us/library/ms187309.aspx

Virtual File Stats

ServerNameDriveNumFilesSizeMBNbrReadsNbrWritesBytesRead_GBBytesWritten_GBIoStallMS
DEVServerG:672719097.18816836383397880329961.03092670440630.400923252102012394598
DEVServerL:451019043.461218901844303810.14563369750121.2950787544236124905
DEVServerT:95120.0001231725821437262623.82694625854787.399448871612371380991

Virtual File Stats by Database

ServerNameDatabaseFileTotal MB ReadTotal MB WrittenTotal I/O CountTotal I/O Wait Time (ms)Size (MB)
DEVSQLServermasterK:\SQLServer\MSSQL\DATA\master.mdf30.2343750000001.046875000000534929988
DEVSQLServermasterK:\SQLServer\MSSQL\DATA\master.log0.7148437500003.45703125000087115732
DEVSQLServerDeveloperD:\SQLServer\\Dev.mdf24.5078125000000.77343750000034129763
DEVSQLServerDeveloperL:\SQLServer\Log\Dev_log.ldf0.7192382812500.5903320312502254631
DEVSQLServerDeveloperM:\SQLServer\Dev_File1.ndf3.6093750000000.0390625000006210713

SQL Server Wait Statistics
Wait Statistics are very useful to identify Problem Areas

Sample Results:

ServerNameWaitTypeWait_SResource_SSignal_SWaitCountPercentage
DEV2016ServerCXPACKET2780766.452709948.9970817.46230378650832.27
DEV2016ServerTRACEWRITE2749593.722749536.5657.16184575731.90
DEV2016ServerPWAIT_DIRECTLOGCONSUMER_GETNEXT2283287.932283287.910.0359326.49
DEV2016ServerIO_COMPLETION138159.91138041.36118.55162117151.60
DEV2016ServerPAGEIOLATCH_SH93710.5593414.69295.8675697721.09
DEV2016ServerLATCH_EX91766.7789777.261989.51279691621.06
DEV2016ServerASYNC_NETWORK_IO79182.2878462.72719.57255859920.92

Mirroring


Mirroring Status

Top