March 2010 - Posts

My first blog post…

I’ve been meaning to start a blog for a while now, (OK, for several years…..) - finally now, here it begins Smile

First post, something really simple but, a wise-man once told me about the best way to improve SQL server performance.

Store Less Data.

That's it.. that's all there is to it...

Over the years, I've seen the following :-

-  a 200Gb database which held 3 days data. Once business requirements changed, we were able to hold only 1 days data in this database.

-  a table developed by DBAs to hold application table cardinality information - that information was collected at 2 hour intervals every day for 7 years ! After 7 years the DBA space-info table had become the largest table in the database - 60 million rows !  It was a simple change to remove alot of the historical intra-day data and change the schedule to run only once per evening. Suddenly that table held 6 million rows instead of 60 million....

- lots of backup and restore history held in msdb. See this post by Brent Ozar for more details on this issue.

Imagine how much faster the backups, DBCC Checks and reindexes ran when the above 3 changes were implemented ?


How often do you review your big databases \ tables to see if you’re actually holding only data that is really required by the business ?

Posted by steveh99999 | 3 comment(s)
Filed under:

DENY select on sys.dm_db_index_physical_stats

I recently saw an interesting blog article by Paul Randal about the performance overhead of querying the sys.dm_db_index_physical_stats.

So I was thinking, would it be possible to let non-sysadmin users query DMVs on a SQL server but stop them querying this I/O intensive DMV ?

Yes it is, here’s how…

1. Create a new login for test purposes, with permissions to access AdventureWorks database only …


USE [AdventureWorks]


2.login as user test and issue command

SELECT  * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'DETAILED')

gets error :- 

Msg 297, Level 16, State 12, Line 1

The user does not have permission to perform this action.

3.As a sysadmin, issue command :-

USE AdventureWorks

GRANT VIEW DATABASE STATE TO [test] or GRANT VIEW SERVER STATE TO [test] if all databases can be queried via DMV.

4. Try again as user test to issue command

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks '),NULL,NULL,NULL,'DETAILED')

-- now produces valid results from the DMV..

5 now create the test user in master database, public role only

USE master


6 issue command :-

USE master

DENY SELECT ON sys.dm_db_index_physical_stats TO [test]

7 Now go back to AdventureWorks using test login and try

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks’),NULL,NULL,NULL,’DETAILED')

Now gets error...

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'dm_db_index_physical_stats', database 'mssqlsystemresource', schema 'sys'.

but the user is still able to query all other non-IO-intensive DMVs.

If the user attempts to view the index physical stats via a builtin management studio report  – see recent blog post by Pinal Dave they get an error also

DMV report screenshot

Posted by steveh99999 | with no comments