SQL Server 诊断查询-(5)

2024-12-08 技术教程

Query #57 Buffer Usage

--Breaksdownbuffersusedbycurrentdatabasebyobject(table,index)inthebuffercache

--Note:Thisquerycouldtakesometimeonabusyinstance

SELECTOBJECT_NAME(p.[object_id])AS[ObjectName],p.index_id,

CAST(COUNT(*)/128.0ASDECIMAL(10,2))AS[Buffersize(MB)],

COUNT(*)AS[BufferCount],p.RowsAS[RowCount],

p.data_compression_descAS[CompressionType]

FROMsys.allocation_unitsASaWITH(NOLOCK)

INNERJOINsys.dm_os_buffer_descriptorsASbWITH(NOLOCK)

ONa.allocation_unit_id=b.allocation_unit_id

INNERJOINsys.partitionsASpWITH(NOLOCK)

ONa.container_id=p.hobt_id

WHEREb.database_id=CONVERT(int,DB_ID())

ANDp.[object_id]>100

GROUPBYp.[object_id],p.index_id,p.data_compression_desc,p.[Rows]

ORDERBY[BufferCount]DESCOPTION(RECOMPILE);

--Tellsyouwhattablesandindexesareusingthemostmemoryinthebuffercache

--Itcanhelpidentifypossiblecandidatesfordatacompression

Query #58 Table Sizes

--GetTablenames,rowcounts,andcompressionstatusforclusteredindexorheap

SELECTOBJECT_NAME(object_id)AS[ObjectName],

SUM(Rows)AS[RowCount],data_compression_descAS[CompressionType]

FROMsys.partitionsWITH(NOLOCK)

WHEREindex_id<2--ignorethepartitionsfromthenon-clusteredindexifany

ANDOBJECT_NAME(object_id)NOTLIKEN'sys%'

ANDOBJECT_NAME(object_id)NOTLIKEN'queue_%'

ANDOBJECT_NAME(object_id)NOTLIKEN'filestream_tombstone%'

ANDOBJECT_NAME(object_id)NOTLIKEN'fulltext%'

ANDOBJECT_NAME(object_id)NOTLIKEN'ifts_comp_fragment%'

ANDOBJECT_NAME(object_id)NOTLIKEN'filetable_updates%'

ANDOBJECT_NAME(object_id)NOTLIKEN'xml_index_nodes%'

ANDOBJECT_NAME(object_id)NOTLIKEN'sqlagent_job%'

ANDOBJECT_NAME(object_id)NOTLIKEN'plan_persist%'

GROUPBYobject_id,data_compression_desc

ORDERBYSUM(Rows)DESCOPTION(RECOMPILE);

--Givesyouanideaoftablesizes,andpossibledatacompressionopportunities

Query #59 Table Properties

--Getsomekeytableproperties

SELECTOBJECT_NAME(t.[object_id])AS[ObjectName],p.[rows]AS[TableRows],p.index_id,

p.data_compression_descAS[IndexDataCompression],

t.create_date,t.lock_on_bulk_load,t.is_replicated,t.has_replication_filter,

t.is_tracked_by_cdc,t.lock_escalation_desc,t.is_memory_optimized,t.durability_desc,t.is_filetable,

t.temporal_type_desc,t.is_remote_data_archive_enabled,t.remote_data_archive_migration_state_desc,t.is_external--newforSQLServer2016

FROMsys.tablesAStWITH(NOLOCK)

INNERJOINsys.partitionsASpWITH(NOLOCK)

ONt.[object_id]=p.[object_id]

WHEREOBJECT_NAME(t.[object_id])NOTLIKEN'sys%'

ORDERBYOBJECT_NAME(t.[object_id]),p.index_idOPTION(RECOMPILE);

--Givesyousomegoodinformationaboutyourtables

--IsMemoryoptimizedanddurabilitydescriptionareHekaton-relatedpropertiesthatwerenewinSQLServer2014

--temporal_type_desc,is_remote_data_archive_enabled,remote_data_archive_migration_state_desc,is_externalarenewinSQLServer2016

Query #60 Statistics Update

--WhenwereStatisticslastupdatedonallindexes?

SELECTSCHEMA_NAME(o.Schema_ID)+N'.'+o.NAMEAS[ObjectName],o.type_descAS[ObjectType],

i.nameAS[IndexName],STATS_DATE(i.[object_id],i.index_id)AS[StatisticsDate],

s.auto_created,s.no_recompute,s.user_created,s.is_incremental,s.is_temporary,

st.row_count,st.used_page_count

FROMsys.objectsASoWITH(NOLOCK)

INNERJOINsys.indexesASiWITH(NOLOCK)

ONo.[object_id]=i.[object_id]

INNERJOINsys.statsASsWITH(NOLOCK)

ONi.[object_id]=s.[object_id]

ANDi.index_id=s.stats_id

INNERJOINsys.dm_db_partition_statsASstWITH(NOLOCK)

ONo.[object_id]=st.[object_id]

ANDi.[index_id]=st.[index_id]

WHEREo.[type]IN('U','V')

ANDst.row_count>0

ORDERBYSTATS_DATE(i.[object_id],i.index_id)DESCOPTION(RECOMPILE);

--Helpsdiscoverpossibleproblemswithout-of-datestatistics

--Alsogivesyouanideawhichindexesarethemostactive

Query #61 Volatile Indexes

--Lookatmostfrequentlymodifiedindexesandstatistics

SELECTo.nameAS[ObjectName],o.[object_id],o.type_desc,s.nameAS[StatisticsName],

s.stats_id,s.no_recompute,s.auto_created,

sp.modification_counter,sp.rows,sp.rows_sampled,sp.last_updated

FROMsys.objectsASoWITH(NOLOCK)

INNERJOINsys.statsASsWITH(NOLOCK)

ONs.object_id=o.object_id

CROSSAPPLYsys.dm_db_stats_properties(s.object_id,s.stats_id)ASsp

WHEREo.type_descNOTIN(N'SYSTEM_TABLE',N'INTERNAL_TABLE')

ANDsp.modification_counter>0

ORDERBYsp.modification_counterDESC,o.nameOPTION(RECOMPILE);

Query #62 Index Fragmentation

--Getfragmentationinfoforallindexesaboveacertainsizeinthecurrentdatabase

--Note:Thisquerycouldtakesometimeonaverylargedatabase

SELECTDB_NAME(ps.database_id)AS[DatabaseName],OBJECT_NAME(ps.OBJECT_ID)AS[ObjectName],

i.nameAS[IndexName],ps.index_id,ps.index_type_desc,ps.avg_fragmentation_in_percent,

ps.fragment_count,ps.page_count,i.fill_factor,i.has_filter,i.filter_definition

FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,N'LIMITED')ASps

INNERJOINsys.indexesASiWITH(NOLOCK)

ONps.[object_id]=i.[object_id]

ANDps.index_id=i.index_id

WHEREps.database_id=DB_ID()

ANDps.page_count>2500

ORDERBYps.avg_fragmentation_in_percentDESCOPTION(RECOMPILE);

--Helpsdeterminewhetheryouhaveframentationinyourrelationalindexes

--andhoweffectiveyourindexmaintenancestrategyis

Query #63 Overall Index Usage – Reads

--IndexRead/Writestats(alltablesincurrentDB)orderedbyReads

SELECTOBJECT_NAME(i.[object_id])AS[ObjectName],i.nameAS[IndexName],i.index_id,

s.user_seeks,s.user_scans,s.user_lookups,

s.user_seeks+s.user_scans+s.user_lookupsAS[TotalReads],

s.user_updatesAS[Writes],

i.type_descAS[IndexType],i.fill_factorAS[FillFactor],i.has_filter,i.filter_definition,

s.last_user_scan,s.last_user_lookup,s.last_user_seek

FROMsys.indexesASiWITH(NOLOCK)

LEFTOUTERJOINsys.dm_db_index_usage_statsASsWITH(NOLOCK)

ONi.[object_id]=s.[object_id]

ANDi.index_id=s.index_id

ANDs.database_id=DB_ID()

WHEREOBJECTPROPERTY(i.[object_id],'IsUserTable')=1

ORDERBYs.user_seeks+s.user_scans+s.user_lookupsDESCOPTION(RECOMPILE);--Orderbyreads

--ShowwhichindexesinthecurrentdatabasearemostactiveforReads

Query #64 Overall Index Usage – Writes

--IndexRead/Writestats(alltablesincurrentDB)orderedbyWrites

SELECTOBJECT_NAME(i.[object_id])AS[ObjectName],i.nameAS[IndexName],i.index_id,

s.user_updatesAS[Writes],s.user_seeks+s.user_scans+s.user_lookupsAS[TotalReads],

i.type_descAS[IndexType],i.fill_factorAS[FillFactor],i.has_filter,i.filter_definition,

s.last_system_update,s.last_user_update

FROMsys.indexesASiWITH(NOLOCK)

LEFTOUTERJOINsys.dm_db_index_usage_statsASsWITH(NOLOCK)

ONi.[object_id]=s.[object_id]

ANDi.index_id=s.index_id

ANDs.database_id=DB_ID()

WHEREOBJECTPROPERTY(i.[object_id],'IsUserTable')=1

ORDERBYs.user_updatesDESCOPTION(RECOMPILE);--Orderbywrites

--ShowwhichindexesinthecurrentdatabasearemostactiveforWrites

Query #65 XTP Index Usage

--Getin-memoryOLTPindexusage

SELECTOBJECT_NAME(i.[object_id])AS[ObjectName],i.index_id,i.name,i.type_desc,

xis.scans_started,xis.scans_retries,xis.rows_touched,xis.rows_returned

FROMsys.dm_db_xtp_index_statsASxisWITH(NOLOCK)

INNERJOINsys.indexesASiWITH(NOLOCK)

ONi.[object_id]=xis.[object_id]

ANDi.index_id=xis.index_id

ORDERBYOBJECT_NAME(i.[object_id])OPTION(RECOMPILE);

--Thisgivesyousomeindexusagestatisticsforin-memoryOLTP

--Returnsnodataifyouarenotusingin-memoryOLTP

Query #66 Lock Waits

--Getlockwaitsforcurrentdatabase

SELECTo.nameAS[table_name],i.nameAS[index_name],ios.index_id,ios.partition_number,

SUM(ios.row_lock_wait_count)AS[total_row_lock_waits],

SUM(ios.row_lock_wait_in_ms)AS[total_row_lock_wait_in_ms],

SUM(ios.page_lock_wait_count)AS[total_page_lock_waits],

SUM(ios.page_lock_wait_in_ms)AS[total_page_lock_wait_in_ms],

SUM(ios.page_lock_wait_in_ms)+SUM(row_lock_wait_in_ms)AS[total_lock_wait_in_ms]

FROMsys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL)ASios

INNERJOINsys.objectsASoWITH(NOLOCK)

ONios.[object_id]=o.[object_id]

INNERJOINsys.indexesASiWITH(NOLOCK)

ONios.[object_id]=i.[object_id]

ANDios.index_id=i.index_id

WHEREo.[object_id]>100

GROUPBYo.name,i.name,ios.index_id,ios.partition_number

HAVINGSUM(ios.page_lock_wait_in_ms)+SUM(row_lock_wait_in_ms)>0

ORDERBYtotal_lock_wait_in_msDESCOPTION(RECOMPILE);

--Thisqueryishelpfulfortroubleshootingblockinganddeadlockingissues

Query #67 UDF Statistics

--LookatUDFexecutionstatistics

SELECTOBJECT_NAME(object_id)AS[FunctionName],execution_count,

total_elapsed_time/1000AS[time_milliseconds],fs.[type_desc]

FROMsys.dm_exec_function_statsASfsWITH(NOLOCK)

WHEREdatabase_id=DB_ID()

ORDERBYOBJECT_NAME(object_id)OPTION(RECOMPILE);

--NewforSQLServer2016

--HelpsyouinvestigateUDFperformanceissues

Query #68 QueryStore Options

--GetQueryStoreOptionsforthisdatabase

SELECTactual_state,actual_state_desc,readonly_reason,

current_storage_size_mb,max_storage_size_mb

FROMsys.database_query_store_optionsWITH(NOLOCK)

OPTION(RECOMPILE);

--NewforSQLServer2016

--RequiresthatQueryStoreisenabledforthisdatabase

Query #69 High Aggregate Duration Queries

--Gethighestaggregatedurationqueriesoverlasthour

WITHAggregatedDurationLastHour

(SELECTq.query_id,SUM(count_executions*avg_duration)AStotal_duration,

COUNT(distinctp.plan_id)ASnumber_of_plans

FROMsys.query_store_query_textASqtWITH(NOLOCK)

INNERJOINsys.query_store_queryASqWITH(NOLOCK)

ONqt.query_text_id=q.query_text_id

INNERJOINsys.query_store_planASpWITH(NOLOCK)

ONq.query_id=p.query_id

INNERJOINsys.query_store_runtime_statsASrsWITH(NOLOCK)

ONrs.plan_id=p.plan_id

INNERJOINsys.query_store_runtime_stats_intervalASrsiWITH(NOLOCK)

ONrsi.runtime_stats_interval_id=rs.runtime_stats_interval_id

WHERErsi.start_time>=DATEADD(hour,-1,GETUTCDATE())

ANDrs.execution_type_desc=N'Regular'

GROUPBYq.query_id),

OrderedDurationAS

(SELECTquery_id,total_duration,number_of_plans,

ROW_NUMBER()OVER(ORDERBYtotal_durationDESC,query_id)ASRN

FROMAggregatedDurationLastHour)

SELECTOBJECT_NAME(q.object_id)AS[ContainingObject],qt.query_sql_text,

od.total_durationAS[TotalDuration(microsecs)],

od.number_of_plansAS[PlanCount],

p.is_forced_plan,p.is_parallel_plan,p.is_trivial_plan,

q.query_parameterization_type_desc,p.[compatibility_level],

p.last_compile_start_time,q.last_execution_time,

CONVERT(xml,p.query_plan)ASquery_plan_xml

FROMOrderedDurationASod

INNERJOINsys.query_store_queryASqWITH(NOLOCK)

ONq.query_id=od.query_id

INNERJOINsys.query_store_query_textASqtWITH(NOLOCK)

ONq.query_text_id=qt.query_text_id

INNERJOINsys.query_store_planASpWITH(NOLOCK)

ONq.query_id=p.query_id

WHEREod.RN<=50

ORDERBYtotal_durationDESCOPTION(RECOMPILE);

--NewforSQLServer2016

--RequiresthatQueryStoreisenabledforthisdatabase

Query #70 Recent Full Backups

--LookatrecentFullbackupsforthecurrentdatabase(Query70)(RecentFullBackups)

SELECTTOP(30)bs.machine_name,bs.server_name,bs.database_nameAS[DatabaseName],bs.recovery_model,

CONVERT(BIGINT,bs.backup_size/1048576)AS[UncompressedBackupSize(MB)],

CONVERT(BIGINT,bs.compressed_backup_size/1048576)AS[CompressedBackupSize(MB)],

CONVERT(NUMERIC(20,2),(CONVERT(FLOAT,bs.backup_size)/

CONVERT(FLOAT,bs.compressed_backup_size)))AS[CompressionRatio],bs.has_backup_checksums,bs.is_copy_only,bs.encryptor_type,--2014 onwords

DATEDIFF(SECOND,bs.backup_start_date,bs.backup_finish_date)AS[BackupElapsedTime(sec)],

bs.backup_finish_dateAS[BackupFinishDate]

FROMmsdb.dbo.backupsetASbsWITH(NOLOCK)

WHEREbs.database_name=DB_NAME(DB_ID())

ANDbs.[type]='D'--ChangetoLifyouwantLogbackups

ORDERBYbs.backup_finish_dateDESCOPTION(RECOMPILE);

--Areyourbackupsizesandtimeschangingovertime?

--Areyouusingbackupcompression?

--Haveyoudoneanybackuptuningwithstripedbackups,orchangingtheparametersofthebackupcommand?

声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。

SQL Server 诊断查询-(5)

热门

最新文章

随机文章

TAGS标签

SQL Server 诊断查询-(5)

相关推荐

相关推荐

热门

最新文章

随机文章

TAGS标签