Wednesday 4 July 2012

Get SQL server Installation and expiry date

Query to get Installed and expiry data of SQL
-- Evaluation Version Expire Date
SELECT create_date AS InstallationDate,
DATEADD(DD, 180, create_date) AS 'Expiry Date'
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000
GO

Tuesday 3 July 2012

Find Index usage

/* How to find how many times indexes has been used */


/*Query which is used to find these indexes related data is mentioned below*/



select O.name as Table_Name, I.name as Index_Name, IU.user_seeks,

IU.User_Scans, IU.System_Seeks, IU.System_Scans

From sys.dm_db_index_usage_stats IU

inner join sys.objects O

ON IU.object_id = O.object_id

Inner join sys.indexes I

ON O.object_id = I.object_id

and IU.index_id = I.index_id

where I.name is not null

order by o.name, i.name

How to find the missing indexes and index usages

/* Find missing indexes and unused indexes */

DECLARE @ObjectID int
SELECT @ObjectID = OBJECT_ID('Table name')
--select count(*) from CORPORATE_GENERAL_OPTION
;WITH preIndexAnalysis
AS (
SELECT
OBJECT_SCHEMA_NAME(t.object_id) as schema_name
,t.name as table_name
,COALESCE(i.name, 'N/A') as index_name
,CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc as type_desc
,NULL as impact
,ROW_NUMBER()
OVER (PARTITION BY i.object_id ORDER BY i.is_primary_key desc, ius.user_seeks + ius.user_scans + ius.user_lookups desc) as ranking
,ius.user_seeks + ius.user_scans + ius.user_lookups as user_total
,COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0) as user_total_pct
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS indexed_columns
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY index_column_id ASC
FOR XML PATH('')), 1, 2, '') AS included_columns
,i.object_id
,i.index_id
,(SELECT QUOTENAME(ic.column_id,'(')
FROM sys.index_columns ic
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY index_column_id ASC
FOR XML PATH('')) AS indexed_columns_compare
,COALESCE((SELECT QUOTENAME(ic.column_id, '(')
FROM sys.index_columns ic
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY index_column_id ASC
FOR XML PATH('')), SPACE(0)) AS included_columns_compare
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
WHERE t.object_id = @ObjectID OR @ObjectID IS NULL
UNION ALL
SELECT
OBJECT_SCHEMA_NAME(mid.object_id) as schema_name
,OBJECT_NAME(mid.object_id) as table_name
,'--MISSING--'
,'--NONCLUSTERED--'
,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
,0 as ranking
,migs.user_seeks + migs.user_scans as user_total
,NULL as user_total_pct
,migs.user_seeks
,migs.user_scans
,0 as user_lookups
,COALESCE(equality_columns + ', ', SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
,included_columns
,mid.object_id
,NULL
,NULL
,NULL
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE database_id = db_id()
AND (mid.object_id = @ObjectID OR @ObjectID IS NULL)
)
,ForeignKeys
AS (
SELECT fk.name + '|PARENT' AS name
,fkc.parent_object_id AS object_id
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.foreign_key_columns ifkc
INNER JOIN sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')), 1, 2, '') AS fk_columns
,(SELECT QUOTENAME(ifkc.parent_column_id,'(')
FROM sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL
UNION ALL
SELECT fk.name + '|REFERENCED' as name
,fkc.referenced_object_id AS object_id
,STUFF((SELECT ', ' + QUOTENAME(c.name)
FROM sys.foreign_key_columns ifkc
INNER JOIN sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')), 1, 2, '') AS fk_columns
,(SELECT QUOTENAME(ifkc.referenced_column_id,'(')
FROM sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('')) AS fk_columns_compare
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL
), IndexAnalysis
AS (
SELECT schema_name
,table_name
,index_name
,type_desc
,impact
,ranking
,user_total
,user_total_pct
,CAST(100 * (user_seeks + user_scans + user_lookups)
/(NULLIF(SUM(user_seeks + user_scans + user_lookups)
OVER(PARTITION BY schema_name, table_name), 0) * 1.) as decimal(6,2)) as estimated_percent
,user_seeks
,user_scans
,user_lookups
,indexed_columns
,included_columns
,STUFF((SELECT ', ' + index_name AS [data()]
FROM preIndexAnalysis iia
WHERE ia.object_id = iia.object_id
AND ia.index_id <> iia.index_id
AND ia.indexed_columns_compare = iia.indexed_columns_compare
AND ia.included_columns_compare = iia.included_columns_compare
FOR XML PATH('')), 1, 2, '') AS duplicate_indexes
,STUFF((SELECT ', ' + index_name AS [data()]
FROM preIndexAnalysis iia
WHERE ia.object_id = iia.object_id
AND ia.index_id <> iia.index_id
AND (ia.indexed_columns_compare LIKE iia.indexed_columns_compare + '%'
OR iia.indexed_columns_compare LIKE ia.indexed_columns_compare + '%')
AND ia.indexed_columns_compare <> iia.indexed_columns_compare
FOR XML PATH('')), 1, 2, '') AS overlapping_indexes
,STUFF((SELECT ', ' + name AS [data()]
FROM ForeignKeys ifk
WHERE ifk.object_id = ia.object_id
AND ia.indexed_columns_compare LIKE ifk.fk_columns_compare + '%'
FOR XML PATH('')), 1, 2, '') AS related_foreign_keys
,CAST((SELECT name
FROM ForeignKeys
WHERE ForeignKeys.object_id = ia.object_id
AND ia.indexed_columns_compare LIKE ForeignKeys.fk_columns_compare + '%'
FOR XML AUTO) as xml) AS related_foreign_keys_xml
FROM preIndexAnalysis ia
UNION ALL
SELECT OBJECT_SCHEMA_NAME(fk.object_id) AS schema_name
,OBJECT_NAME(fk.object_id) AS table_name
,fk.name AS index_name
,'--MISSING FOREIGN KEY--'
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,fk.fk_columns
,NULL
,NULL
,NULL
,NULL
,NULL
FROM ForeignKeys fk
LEFT OUTER JOIN preIndexAnalysis ia
ON fk.object_id = ia.object_id
AND ia.indexed_columns_compare LIKE fk.fk_columns_compare + '%'
WHERE ia.index_name IS NULL
)
SELECT schema_name
,table_name
,index_name
,type_desc
,impact
,user_total
,user_total_pct
,estimated_percent
,user_seeks
,user_scans
,user_lookups
,indexed_columns
,included_columns
,duplicate_indexes
,overlapping_indexes
,related_foreign_keys
FROM IndexAnalysis
ORDER BY 1, 2, 3
SQL server versions , packs ,Builds and new patches release dates.

 
SQL Server 2012
11.0.2316
12 Apr 2012
11.0.2100.6
7 Mar 2012
SQL Server 2008 R2
10.50.2811
16 Apr 2012
10.50.2806
22 Feb 2012
10.50.2796
9 Jan 2012
10.50.2789.0
17 Oct 2011
10.50.2772.0
16 Aug 2011
10.50.2769.0
16 Sep 2011
10.50.2500.0
11 Jul 2011
10.50.1815
17 Apr 2012
10.50.1810
21 Feb 2012
10.50.1809
9 Jan 2012
10.50.1807.0
19 Oct 2011
10.50.1804.0
23 Aug 2011
10.50.1797.0
16 Sep 2011
10.50.1777.0
16 Jun 2011
10.50.1765.0
21 Feb 2011
10.50.1753.0
20 Dec 2010
10.50.1746.0
18 Oct 2010
10.50.1734.0
17 Aug 2010
10.50.1720.0
25 Jun 2010
10.50.1702.0
18 May 2010
10.50.1600.1
SQL Server 2008 R2 RTM
12 Apr 2010
SQL Server 2008
10.00.5775
20 Mar 2012
10.00.5770
16 Jan 2012
10.00.5768
22 Nov 2011
10.00.5766
18 Oct 2011
10.00.5500
6 Oct 2011
10.00.4330
19 Mar 2012
10.00.4326
30 Jan 2012
10.00.4323
21 Nov 2011
10.00.4321
20 Sep 2011
10.00.4316
18 Jul 2011
10.00.4285
16 May 2011
10.00.4279
21 Mar 2011
10.00.4272
17 Jan 2011
10.00.4266
15 Nov 2010
10.00.4000
29 Sep 2010
10.00.2850
19 Sep 2011
10.00.2847
18 Jul 2011
10.00.2816
22 Mar 2011
10.00.2812
16 May 2011
10.00.2808
17 Jan 2011
10.00.2804
15 Nov 2010
10.00.2799
21 Sep 2010
10.00.2789
19 Jul 2010
10.00.2775
17 May 2010
10.00.2766
15 Mar 2010
10.00.2757
18 Jan 2010
10.00.2746
24 Nov 2009
10.00.2734
22 Sep 2009
10.00.2723
21 Jul 2009
10.00.2714
18 May 2009
10.00.2710
16 Apr 2009
10.00.2531
7 Apr 2009
10.00.1835
15 Mar 2010
10.00.1828
18 Jan 2009
10.00.1823
16 Nov 2009
10.00.1818
21 Sep 2009
10.00.1812
21 Jul 2009
10.00.1806
18 May 2009
10.00.1798
17 Mar 2009
10.00.1787
19 Jan 2009
10.00.1779
17 Nov 2008
10.00.1763
22 Sep 2008
10.00.1600
SQL Server 2008 RTM
6 Aug 2008
SQL Server 2005
9.00.5266
21 Mar 2011
9.00.5259
22 Feb 2011
9.00.5254
20 Dec 2010
9.00.5000
17 Dec 2010
9.00.4325
21 Mar 2011
9.00.4317
21 Feb 2011
9.00.4315
20 Dec 2010
9.00.4311
18 Oct 2010
9.00.4309
17 Aug 2010
9.00.4305
23 Jun 2010
9.00.4294
19 Apr 2010
9.00.4285
16 Feb 2010
9.00.4273
21 Dec 2009
9.00.4266
19 Oct 2009
9.00.4230
17 Aug 2009
9.00.4226
16 Jun 2009
9.00.4220
21 Apr 2009
9.00.4211
17 Feb 2009
9.00.4207
20 Dec 2008
9.00.4053
13 Oct 2009
9.00.4035
16 Dec 2008
9.00.3356
21 Dec 2009
9.00.3355
19 Oct 2009
9.00.3330
18 Aug 2009
9.00.3328
16 Jun 2009
9.00.3325
21 Apr 2009
9.00.3315
17 Feb 2009
9.00.3310
10 Feb 2009
9.00.3301
SQL Server 2005 SP2 CU11
15 Dec 2008
9.00.3294
20 Oct 2008
9.00.3282
18 Aug 2008
9.00.3257
16 Jun 2008
9.00.3239
14 Apr 2008
9.00.3233
SQL Server 2005 QFE Security Hotfix
8 Jul 2008
9.00.3228
18 Feb 2008
9.00.3215
17 Dec 2007
9.00.3200
15 Oct 2007
9.00.3186
20 Aug 2007
9.00.3175
28 Jun 2007
9.00.3161
SQL Server 2005 SP2 CU1
15 Apr 2007
9.00.3152
7 Mar 2007
9.00.3077
10 Feb 2009
9.00.3054
5 Apr 2007
9.00.3042.01
5 Mar 2007
9.00.3042
SQL Server 2005 SP2
1 Feb 2007
9.00.2047
SQL Server 2005 SP1
9.00.1399
SQL Server 2005 RTM
1 Nov 2005