TSQL to identify Compression Type on Indexes within a SQL 2008 or greater database

I came across a need to identify which indexes within a SQL 2008 R2 database that have Compression enabled and more importantly those that do not. I could not find a specific query to do this online but have come up with the following.

This query will not only report the Indexes and their compression type for User Tables it will also generate the statements to enable PAGE or ROW compression, or remove the compression from the table.

SELECT sys.schemas.name AS [SchemaName]
, sys.objects.name AS [TableName]
, ISNULL(sys.indexes.name, '') as [IndexName]
, SUM(sys.partitions.Rows) AS [RowCount]
, sys.partitions.data_compression_desc AS [CompressionType]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' as [PAGECompressionTSQL]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)' as [ROWCompressionTSQL]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)' as [REMOVECompressionTSQL]
FROM sys.partitions
INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id
AND sys.objects.type = 'U' --USER_TABLE
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id
LEFT JOIN sys.indexes ON sys.partitions.object_id = sys.indexes.object_id
AND sys.partitions.index_id = sys.indexes.index_id
WHERE sys.partitions.index_id >= 0 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(sys.partitions.object_id) LIKE '%' --Enter the table name here to restrict the results
GROUP BY sys.schemas.name
, sys.objects.name
, ISNULL(sys.indexes.name, '')
, data_compression_desc
ORDER BY data_compression_desc
, SUM(sys.partitions.Rows) DESC OPTION (RECOMPILE);

One step closer..... SQL 2012 RC0 is released

Well today we are one step closer to the release of SQL 2012 (aka Denali) with the release of RC0.... actually it was yesterday but I was burried in opperational support yesterday. Anyway this is very exciting to me as there are so many features that offer great new capabilities.

Here's some links to what others are writing about this because why should I re-invent the information:

http://blogs.technet.com/b/sqlman/archive/2011/11/18/sql-server-2012-rc0-available-now.aspx

http://blogs.msdn.com/b/analysisservices/archive/2011/11/17/announcing-microsoft-sql-server-2012-release-candidate-0-rc0.aspx

http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx


You can get the RC0 download at http://www.microsoft.com/download/en/details.aspx?id=28145

Enjoy!

Recovering/Restoring the Resource system database in SQL Server 2005

Here is the scenario I have been working through in the LAB today:

The Resource database (mssqlsystemresource) has become corrupt on a SQL 2005 SP4 instance. You are not sure why the database has become corrupt but when you try and start the SQL Server DB Engine the following is reported in the SQL Server ERRORLOG:

2011-08-01 14:57:23.89 spid5s Starting up database 'mssqlsystemresource'.
2011-08-01 14:57:23.91 spid5s Error: 5172, Severity: 16, State: 15.
2011-08-01 14:57:23.91 spid5s The header for file '\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf' is not a valid database file header. The PageAudit property is incorrect.
2011-08-01 14:57:23.95 spid5s Error: 945, Severity: 14, State: 2.
2011-08-01 14:57:23.95 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-08-01 14:57:23.97 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.



We know that we cannot backup/restore the Resource database using SQL Server (as per http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx) and you could restore the physical file from the server's file system backups. Unfortunately though the file is unable to be recovered from the server's file system backup for an unknown and unrelated external factor.

Never fear there is a simple fix to this. Reinstall the Service Pack as that will recreate the Resource database.... Hurray

Here's the steps and things to note when performing the Service Pack install though:
1. Launch the Service Pack install
2. Accept the terms and conditions and click Next
3. When asked to select the Feature to update you will notice that the Database Services for the required instance will already report "Upgraded". Simply select the instance though as this will re-apply the service pack.
4. Complete the service pack install

Once the Service Pack has been installed you should be able to start the SQL Server Service for the required Instance.

TIPS: You can use the following TSQL to grab information about your SQL Instance and Resource Database. These were taken from http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx

SELECT @@VERSION
SELECT SERVERPROPERTY('ResourceVersion');
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');


SQL 2008 / SQL 2008 R2
For the process to rebuild the Resource database in SQL 2008 follow the instructions at http://msdn.microsoft.com/en-us/library/dd207003.aspx

Happy Disaster Recovering everyone :)

Search Stored Procedure Code

Came across an interesting situation. The database that I was working with was missing from CVS, and I needed to be able to check what Stored Procedures were using Temporary tables. Thankfully I found a blog by one of my favorite authors which saved the day.

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/

TSQL to list all Permissions within a Database

Came across a situation today while trouble shooting a problem with replication where the subscription database has continually been getting modified by an unknown source and thus breaking replication. To assist with investigating this issue there was a need to look at all the permissions assigned to all users and roles within the database both at the Publisher and the Subscriber..... so here is the TSQL I wrote to help with this.

select sys.database_permissions.class_desc
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, sys.database_principals.name as [grantee_principal_name]
, ISNULL(sys.schemas.name, '') as [schema_name]
, ISNULL(AllObjects.name, '') as [object_name]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'GRANT ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'GRANT ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Grant_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'DENY ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'DENY ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Deny_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'REVOKE ' + sys.database_permissions.permission_name + ' FROM [' + sys.database_principals.name + '];'
ELSE 'REVOKE ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] FROM [' + sys.database_principals.name + '];'
END) as [Revoke_Perms_Stmnt]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
--AND sys.database_principals.name = 'public' --uncomment this line to restrict the output to a single principal
LEFT JOIN (
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.objects
UNION
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
ORDER BY sys.database_principals.name
, sys.database_permissions.class
, sys.schemas.name
, AllObjects.name

Infrastructure Saturday - Wrap Up

Firstly a huge thank-you to everyone that attended the session on Saturday, and also a thank-you to BenF for assisting with the presentation (and letting me out of the cage for the day).

I am working with the organizers of Infrastructure Saturday to make the slides available for download, so stay tuned for more information on that front.

Some interesting questions were raised during the session and I thought I would provide some additional information here for those that were interested.

During the session some questions/discussions were raised about Virtalisation. I won't go into this in this post because I am actually expecting some interesting news on that front to come out of the SQLPASS which is being held this week in Seattle which although I am not attending I will be following closely.

At the end of the session I was approached with the question "We currently have multiple SQL 2005/2008 servers but our monitoring is only of the server layer, can you suggest/recommend how we could monitor the SQL layer".

In my experience a combination of monitoring solutions can offer a much more holistic view of your environment. This also helps when monitoring multiple different Operating Systems (e.g. Windows and Linux). Of cause this is based on my experience with monitoring 220+ servers.

The reason a combination of monitoring solutions can offer more depth is because some of monitoring platforms offer better network monitoring than others, and other monitoring platforms offer better Application / Software monitoring.

The challenge with monitoring a SQL Server is that you not only need to monitor the Infrastructure (e.g. ICMP, TCP Port tests, etc) but you need to be able to check Event Logs and that Services are running. Most monitoring systems can provide those tests, however you also need to monitor the actual SQL Instance layer and some of the best ways of doing that is through some form of script which can run a TSQL statement or use SMO to check aspects of the environment and return a status code to the Monitoring environment.

Some of the key SQL layer aspects that a good monitoring solution should be able to check are:
- SQL Server Memory
- SQL Server Error Log
- SQL Server Settings
- Database File Free Space
- Database Status
- Database Settings (for enforcing environment settings)
- Blocking Locks

My experience has been largely about using Microsoft Operations Manager (aka MOM) and custom VBScripts to ensure that our business rules are enforced. This has also allowed a number of automated actions to be implemented while allowing for the dynamics of the farm.

Here are some links to some monitoring platforms to look at:

Microsoft System Center Operations Manager (aka SCOM, the latest release of MOM)
http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx
If I had to recommend an enterprise software it would be SCOM, only because that is where my experience has been with. Of cause there are other offerings from EMC and other vendors which might be more suitable to your environment.

If your looking for some free monitoring solutions purhaps the following might be of interest:

Zenoss
http://www.zenoss.com/ or http://community.zenoss.org
I have had some experience with using this and it can offer a good monitoring tool for network monitoring (ICMP) and auto discovery. It does take some amount of tweaking to get to work well with Windows (as with any Unix based monitoring tool) however it can provide that first level monitoring quite well and there are a number of ZenPacks available to provide SQL Server monitoring. However I do find it quite challenging to come up with a in-depth SQL monitoring from it, but this is purely because I am use to a Windows Scripting environment.

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
I previously blogged about this (and some other open-source tools). This is still my fav free open-source offering, and a great option if you do not have but budget for SCOM and if you are willing to write your own client front-end, or if you need to plug into an existing monitoring front-end.

I hope this information helps you to find a way to monitor your SQL database environment. Feel free to post comments should you need more info.


Thanks again to everyone that attended the session.

Infrastructure Saturday

Today is Infrastructure Saturday at Microsoft Brisbane, I'll be there presenting from 12:35pm – 1:20pm in Theater 2......

More info at http://infrastructuresaturday.org/