SQL Server Blog

I’ve been in numerous situations now in which people require security in their SQL Server (obviously) but find that the provided db_dataReader and db_dataWriter aren’t quite good enough. They require execute rights on all procedures, but find this is hard to set up.

This is another useful piece of code (well, I find it handy anyway) which I wrote to help populate a dashboard.



It’s very simple but, based on a quick internet search, fulfils a gap people seem to struggle with.

First things first… NEVER shrink your database unless you REALLY have to.



Now that’s out of the way, this is one of those occasions when I decided that a shrink was a bearable approach, however it wasn’t without issue and hence I found this funky piece of code.

This was an incredibly cool thing that I learnt the other week whilst attending a SQLSkills course. I have previously made mention of Vertical Partioning and how useful it can be, BUT it does have the downside of re-architecting… this can change that.



Now, before you get too excited, you do need to have proper LOB columns in your table for this to work. Therefore if you have a few hefty varchar(1000) columns then you’re stuck with the re-architecting method you’ll find in an earlier blog post, but if you’re using a true LOB like varchar(max) then this could be for you.

This is a simple piece of code pinched from the Microsoft Performance Dashboard, but it’s useful and needs highlighting on its own as I tend to use it extensively.



Effectively it shows not only the CPU being used by SQL Server but also the CPU consumed by other processes.

This is actually a Windows level setting, but something that is hugely beneficial to a SQL Server and should be enabled where possible.



It basically allows database data files to instantly allocate new space (autogrowth, creating new database, restoring a database, adding datafiles etc) rather than zero initializing them.

This post comes about due to my spending an annoying large amount of time on a code failure that turned out to be utterly infuriating, but ultimately interesting as well.



It involves the use of named constraints within temp tables.

I’ve been wanting to attend one of these ultimate training courses from Paul Randal and Kimberly Tripp for a long time now and finally I managed to get the time off, the money together and book myself on.



Therefore at the beginning of this month I was out in Chicago partaking in some of the most intensive, yet enjoyable, SQL training money can buy. (Aside from my own, of course :o))

This was something that I have wanted to be able to do for a while. I like having wait stats to look at (I’ll do a blog about those at some point - this is for people who already know what they are)… but until Extended Events came about these were only available, to my knowledge, at the server level. This was great, but what if you wanted to know what your specific query was doing?