Yusuf Ozturk » 2015

Blogroll

Badges

Community

MSSQL Server Index Reports

Posted in Windows Server | No Comment | 1,748 views | 25/11/2015 15:00

This is a query of mine, which gives you detailed information about indexes on one query:

SELECT SO.NAME AS TableName, PS.object_id AS ObjectId, SI.NAME AS IndexName, PS.index_id AS IndexId, SUM(PS.RESERVED_PAGE_COUNT*8) TotalStorage, SUM(PS.USED_PAGE_COUNT*8) UsedStorage, SUM((PS.RESERVED_PAGE_COUNT - PS.USED_PAGE_COUNT)*8) FreeStorage,
SUM(CASE WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT ELSE 0 END) AS ROW_COUNT,
MAX(round(Avg_Fragmentation_In_Percent, 2)) AS 'AvgFragmentationInPercent',
MAX(fragment_count) AS FragmentCount,
MAX(ips.page_count) AS PageCount,
MAX(ius.user_scans) AS UserScans, MAX(ius.user_seeks) AS UserSeeks, MAX(ius.user_lookups) AS UserLookups
FROM
SYS.DM_DB_PARTITION_STATS PS
INNER JOIN sys.dm_db_index_physical_stats(5,NULL,NULL,NULL,'LIMITED') AS ips
ON ps.object_id = ips.object_id AND
       ps.index_id = ips.index_id
INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID
LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID
AND SI.INDEX_ID = PS.INDEX_ID
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = 5 AND ps.object_id = ius.object_id AND ps.index_id = ius.index_id
WHERE
SO.IS_MS_SHIPPED = 0
GROUP BY so.name, ps.object_id, si.name, ps.index_id
ORDER BY UserLookups DESC

You can also add Schema column if you have different db schemeas.

MSSQL Database Performance Tricks

Posted in Windows Server | No Comment | 1,898 views | 10/11/2015 15:14

Check following query to improve your database performance:

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'11000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'11000'
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

This is a note for myself. So you should edit config to meet your requirements.

MSSQL Database Index Fragmentation Reports

Posted in Windows Server | No Comment | 2,202 views | 10/11/2015 15:10

You can get Database Index Fragmentation Reports with following query:

SELECT object_name(ps.object_id) AS [name], 
	ps.index_id,
	i.name AS IndexName,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ps
LEFT JOIN sys.indexes i ON i.object_id=ps.object_id AND i.index_id=ps.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ps.page_count  > 100
ORDER BY ps.avg_fragmentation_in_percent DESC

You can check MSDN for more information about sys.dm_db_index_physical_stats.

Getting Stored Procedure Stats from MSSQL

Posted in Windows Server | No Comment | 1,926 views | 10/11/2015 15:07

Following query will give you details about SP usage:

SELECT DB_NAME(database_id) DBName,
 OBJECT_NAME(object_id) SPName,
 last_execution_time LastExec,
 last_elapsed_time/1000 LastTimeMS,
 last_worker_time/1000 LastWorkerCPU,
 last_physical_reads LastPReads,
 last_logical_writes LastLWrites,
 last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats 
WHERE database_id = DB_ID()

If you need to get all custom SPs in one query:

SELECT DB_NAME(database_id) DBName,
 OBJECT_NAME(object_id) SPName,
 last_execution_time LastExec,
 last_elapsed_time/1000 LastTimeMS,
 last_worker_time/1000 LastWorkerCPU,
 last_physical_reads LastPReads,
 last_logical_writes LastLWrites,
 last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats 
WHERE database_id > 4 AND database_id < 32767

Just filtered last stats, but you can use SELECT * to get all stats.

Getting all table column names of MSSQL database

Posted in Windows Server | No Comment | 1,843 views | 10/11/2015 15:01

If you need to get all table column names from MSSQL database, you can use following query:

SELECT 
o.name, c.name 
FROM sys.columns c
INNER JOIN sys.objects  o ON c.object_id=o.object_id
ORDER BY o.name, c.column_id

That will output all column names.