Categories
Sponsors
Archive
Blogroll
Badges
Community
|
Posted in Windows Server | No Comment | 1,747 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 |
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.
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 |
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.
Posted in Windows Server | No Comment | 2,201 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 |
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.
Posted in Windows Server | No Comment | 1,925 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() |
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 |
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.
Posted in Windows Server | No Comment | 1,842 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 |
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.
|