A lot has been said and written about the financial struggles facing PASS during the global pandemic.  I would like to address this situation for our local users in the northern Ohio area who are rightfully concerned about the future of their local user group. Before I do, please be aware that this post has not been reviewed by nor approved by any member or leader of the Ohio North SQL Server User Group or PASS.  All opinions are strictly my own. Background Our local user group is the PASS Local Group for Northern Ohio.  PASS, as an organization, is facing financial difficulties due toRead More →

By now it might be apparent to both of my regular readers that I haven’t posted anything new in quite a while. I’m here to say that it’s probably going to be that way for a little while longer. Recently, I was approached about writing content for MSSQLTips.com.  It was a very flattering offer that I gladly accepted.  They have a tremendous library of content to which I’m very proud to be able to add. You can see my author page here.  As of this writing, I already have several live Tips. There just aren’t enough hours in the day to write for both MSSQLTipsRead More →

SQL Server corruption usually happens when a data page is written to disk and the disk subsystem doesn’t write what SQL Server expects.  Or it does write what is expected, but somehow changes it before the next write.  As one can imagine, this should really never happen. But once is a great while it does.  This post will show how page verification can help determine there is corruption before a DBCC CheckDB finds it.  This early warning system can be very effective. What is Page Verification? Page verification is a process in SQL Server where the engine writes extra data to the header of theRead More →

I’m going to start a series of posts regarding corruption detection, prevention, and correction.  In order to detect corruption we must first have a corrupt database.  This post is going to show how to purposely corrupt a database.  I’ll refer back to this post often as it will be the basis of most of the future posts in this series which will pick up where this leaves off. Step 1.  Create a new database.  You didn’t think I was going to ask you to corrupt a real database did you?  We will create one table with some names in it.  The table is abnormally wide onRead More →

It’s been a week since I came back to Ohio after a week at PASS Summit in Seattle.  I’m about 6 days late, but I wanted to take a moment to opine over the things I learned and offer some overdue updates and thanks. I had a fantastic time at the Washington State Conference Center.  They do such a nice job there.  The food is great.  The facilities clean.  I didn’t witness a single issue with A/V.  There were so many volunteers you couldn’t possibly get lost.  Thank you everyone behind the scenes that make this such a great event. I owe a special thanksRead More →

Thank you to everyone that attended my session, I have indexes, but do I have the right indexes? at IT/DevConnections this week in Dallas.  I was truly honored that so many of you chose to attend my session. Please find a zip file containing the slide deck and scripts at this link.Read More →

This is the final in a series of posts about query plans.  The third entry explained how the plans are stored in a cache to be reused.  This post will cover the opposite.  When are plans NOT able to be reused?  What would cause a plan to need to be compiled again? There are several reasons that a query plan would need to be compiled again, but they can be boiled down to a few popular reasons. The first one is simple.  The plan cache is stored exclusively in memory.  If there is memory pressure on the instance SQL Server will eject plans from cacheRead More →

The prior post in this series was able to fix a parameterization issue by removing dynamic SQL and replacing it with a traditional SQL statement.  But sometimes that can’t be done because dynamic SQL is a requirement.  In that case the dynamic statement can still be parameterized using 2 optional parameters to the procedure sp_ExecuteSQL. The first parameter of sp_ExecuteSQL is the only required parameter and contains the SQL statement to be executed.  What is less known is that this statement can reference variable names.  By using variable names instead of hard coded values the query execution plan used by that SQL statement is more likelyRead More →

This is the 5th in a series of posts about query plans.  The first post covered what a query plan is and how to read it.  The second post described the effort required to make a query plan.  The third entry explained how the plans are stored in a cache to be reused.  The 4th post showed how the optimizer finds appropriate plans in cache.  This post will cover how poor coding practices can lead to excessive plans being stored in the cache. Plans are found in cache to be reused only when an exact match of the SQL query is found.  This works great until constant valuesRead More →