Do You Use “Forced Parameterization”?

image

In SQL Server 2005 and later, there is a database option called “forced parameterization”. When it is turned on, it overrides the default “simple parameterization” normally used by SQL Server. It does this by forcing most SELECT, INSERT, UPDATE, and DELETE statements to be parameterized, potentially boosting performance of databases by reducing the number of query compiles and recompiles that need to occur.

As with many “options” in SQL Server, whether this option should be turned on for a particular database is not a simple decision. So I was curious to see how many DBAs actually employ this option. I ran the above poll on my website, and had two surprises. The first surprise is that about 19% of responders said they use it, which I thought was high. The second surprise is that about 44% of responders have not even heard of this option.

If you are one of the 44% who aren’t familiar with forced parameterization, I suggest you read the following to learn more about it. While this database option can be very handy in specific cases, it can also hurt performance in other databases. If you try this option, be sure to perform a benchmark before implementing it, then another benchmark after implementing it, and seeing if you get the behavior you expect. Of course, perform this experiment in a test environment, not in production.

http://technet.microsoft.com/en-us/library/ms175037.aspx 

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/forced-parameterizationwhen-should-i-use-it-137149

SQL Server MVP Deep Dives: Volume II

image

Two years ago when SQL Server MVP Deep Dives: Volume 1 was released, I had the opportunity to contribute two chapters. In SQL Server MVP Deep Dives Volume 2, I not only had the opportunity to contribute a chapter, but the privilege of being the section editor of the “Performance Tuning and Optimization” section of the book. I would like to thank all of the following writers in my section for all their hard work, and for getting me their drafts on schedule. I also want to thank Grant Fritchey, who helped me coordinating the technical editing of the chapters.

Continue reading

Database Properties Health Check

For some time now, I have been working on a new book called How to Perform a SQL Server Health Check. I have written over 300 pages of it, and I am only about half way done. In the meantime, I have published a short except from the book on Simple-Talk.com. The article focuses specifically on database properties, and also includes a T-SQL script I wrote, and a PowerShell script written by Phil Factor, that will help you collect your existing database properties, making it easier for you to determine your existing database property settings. This is just a taste of the things to come from my new book. Now, I just need to find some extra time to finish writing it.

Learning From the Mistakes of Other DBAs

I have been a judge for the Exceptional DBA of the Year Awards ever since its inception, and one of the questions that is asked on the application form is “What is the biggest mistake you have made as a DBA, and how did you deal with it”. This question produces some interesting answers, many of which provide important lessons for all DBAs. I have collected some of those responses from past years, not for the current 2011 Exceptional DBA of Year Awards entries, which I think you will find educational. I have edited these entries for readability and to remove any potential confidential information. Hopefully you will learn from these DBAs’ mistakes, and not make the same ones yourself.

Continue reading

Do You Ever Physically Defragment Your SQL Server MDF & LDF Files?

image

Every since the first file was written to a floppy disk drive using DOS, physical file fragmentation has been a problem. Essentially, when the OS writes a file to a disk subsystem, and if contiguous clusters are not available, they are written elsewhere on disk. So when a file is stored on disk in a non-contiguous manner on a disk subsystem, the file is considered to be physically fragmented. Physical file fragmentation can contribute to an additional load on your I/O subsystem and reduce performance because the disks have to work harder to read and write data. This is because the heads on disk drive have to randomly jump around to different physical locations on the disk to find all of the data, instead of reading and writing the data sequentially, as when the data is contiguous.

Continue reading

Do You Enable “Optimize for Ad Hoc Workloads”?

image

Most of the time when I run a poll on my blog, I run it for at least a month so that I can get enough results in order for the data to be more or less representative of the SQL Server community. What has been odd about this poll, is that even though I have run it longer than any other poll on the website, it has had one of the fewest response rates.

When I look at the results, my best guess is that people who have looked at the poll weren’t familiar with the question, and because of that, ignored responding to it, instead of admitting that they didn’t know what it was. Or, perhaps it is because this feature is only available in SQL Server 2008 and higher, and not everyone is running this version yet.

Continue reading

SQLServerCentral.com Track at Orlando SQL Server Connections

SQLServerConnectionsHotel

This past week, about 1,200 developers, IT professionals, and DBAs spent three days attending DevConnections at the J.W. Marriott Resort and Conference Center in Orlando, FL. While the above photo depicts a perfect Florida spring day, most of the time the weather was terrible with storms and rain. It really didn’t matter much for the attendees, as we stayed inside and focusing on learning and networking.

Continue reading