jump to navigation

Partitioning best practices May 16, 2008

Posted by msrviking in Performance tuning.
add a comment

Well, I am in a mood to get more entries into the list (maybe I was waiting for the Friday evening, so that I can fill in the missed entries Wink).

Here is the entry all about. I had to review partition design for one of the fat (oops did I just say fat!), yes, it is a fat fact table in a SSAS instance. The size of the table was somewhere around 360+ GB (data and indexes) and was partitioned and I had to review the partitioning strategy. I had prepared some best practices to check the design compliance, and here is the list

•    Avoid having partitions with more than 20 million rows
•    Combine partitions when a small partition has less than two million rows
•    The filtering key of the query should have the partition key
•    Each filegroup should logically represent only one partition
•    Each filegroup (mapped to partition) should have only one file
•    The filegroups can be on multiple disks
•    Partition function should map the boundary points to appropriate filegroups
•    Size of each partition shouldn’t exceed 250 MB
•    No of partitions in a database should not be greater than 100
•    The partition key of multiple tables (related tables) should necessarily use same partition function to get best alignment
•    The aligned tables should have equi-join predicate on the partitioned column to get collocation
•    The partition key must exist as a single column in the table and must meet a criteria
•    Use CHECK constraints to restrict domain of values to a specific range
•    Paritioned index should use the same partition function to optimize performance
•    Partitioned index can use the same partition scheme to achieve storage alignment
•    The non-volatile partitions can be placed on read-only filegroups
•    Non-clustered indexes should be partitioned to achieve storage alignment and colocation
•    Tempdb should have multiple files (one file per processor)
•    Tempdb should be separated on to a different disk controller with RAID 5
•    Partitioning aggregation cubes

Can you guys let me know what is missing or to be changed in this list?

Happy reading!



What is WITH RECOMPILE option? May 16, 2008

Posted by msrviking in Performance tuning.
add a comment

Hello Guys,

I had a question from one of the developers asking me "What is WITH RECOMPILE?" and "When should we use this option?".

Well, here is what I could tell the developer (An abridged version of the mail I had replied back)

Read this note before you read the reasons what  is recompile and when to use it.
·         Logical reads mentioned in here is measure of how your query is doing, and you could get it by running SET STATISTICS IO.
·         Reusing a cached plan is always good, but in exceptions you will want to use WITH RECOMPILE.

Let us take an example. Say you have a query

Select * from <tabname> where <col1> = @var1 (@var1 = 1000 being an input value from the application). When this query is executed, the execution plan shows up a seek, using a non clustered or clustered index on the column col1 and this is always good. And the results of STATISTICS IO is 2 logical reads.

And, when you have the query Select * from <tabname> where <col1> = @var1 (@var1 = 10 being an input value from the application) executed, the execution plan may still show a seek again using same clustered and non clustered index. Great, this sounds good (reuse of execution plan) but the STATISTICS IO results show 30 logical reads.

Now, let us clear up the the plan cache of SQL Server by using some system command (DBCC FREEPROCCACHE) and run the same bunch of queries in a reverse order.

Select * from <tabname> where <col1> = @var1 (@var1 = 10 being an input value from the application). When this query is executed, the execution plan shows up a table scan with 10 logical reads.

Alright, now we execute the other query Select * from <tabname> where <col1> = @var1 (@var1 = 1000 being an input value from the application) the execution plan shows up a table scan with 20 logical reads.

So what should alarm you, is that query with input value 1000 took 2 logical reads with a seek and 20 logical reads with a table scan. And the query with input value 10 shows up 30 logical reads in a seek operation and 10 logical reads in a table scan.

Ah, you see confusing figures of (2, 20) and (30, 10) and you could be wondering what do you do now and which plan is the best? Well, each plan is best for the respective input parameters.

So what will you do now, to let the best plan be in place based on input parameters? You will use WITH RECOMPILE.

What does WITH RECOMPILE do? Every time there is a new input value, the existing reusable plan is not utilized and a new plan is created. So you are safe with having 2 logical reads with seek operation when input value is 1000 and 10 logical reads with scan operation when input value is 10.

Now, one may appreciate this statement after reading the above example.

“WITH RECOMPILE option, tells SQL Server to rebuild the plan each time the procedure is called. Although this means you get a good plan for your input parameters, it also means you never get the benefit of reusing a saved plan. In SQL Server 2000 and 7.0, a recompile always means a recompile of the entire procedure, even if only one statement in the procedure is using a suboptimal execution plan.”

Why is your sp performance gone bad after adding WITH RECOMPILE? By using WITH RECOMPILE, you are making the sp recompile every time and for a complex stored procedures eliminating recompilation time and this can make a noticeable performance difference.

There is an excellent whitepaper from microsoft on recompilation. Here is the link for your further reading – Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Enjoy reading, and do let me know what do you all think.


PS.: The last part of the entry in quotes is by my favorite SQL Server Guru – Kalen Delaney.

Why is shrinkdatabase not releasing space to the drive? May 7, 2008

Posted by msrviking in DB Administration.
add a comment

Back again with something very peculiar. My day started off
with one one of my fellow mates coming up to me.

Here is the conversation

My Buddy: "There is a 19GB table sitting out in one of the
environments instance, and I want to get rid of it."
Me: "Why do you want that table to out of the database, something
wrong?" (I was having several questions, but kept myself drawn into limits
so that I don’t bug him with my questions.)

My Buddy: "I need to release space to one of the drives on which
the database is sitting? Can I shrink only that table after deleting or
truncating the data?"
Me: "No, you can’t shrink a table to reclaim space for the
drive as long as you have other objects sitting in the same filegroup. You
could shrink the table alone if you have only that table in the
filegroup. The other way to reclaim the space could be by dropping the table
and shrinking the files of the filegroup."

Alright, now what! The database physical structure is that there are other
objects along with this 19GB table. So, my buddy went ahead and dropped the
table, and started shrinking the database to reclaim the space. Surprise, it
didn’t work. The database didn’t give back the space to the drive. After
several attempts, some service restarts, and txn log backups my buddy got back
to me with the issue that "It is not happening". I took few minutes
to think thro’ the sequence of shrinking I would do and sent another salvo of
questions "What are the commands you have run? What time did you run? Did
you see any errors after shrinking? What is the current size of the database
and database files? Were there anyone else on the instance during this
maintenance work…” (I am restricting the questions, so that you guys don’t
get psyched of the kind of naive questions I keep asking all the time, to everyoneWink). 
Well here are the answers in the order of questions asked "dbcc
shrinkdatabase(databasename, truncateonly), evening (5 PM – 8 PM), No, ‘x GB –
DB, used space – 45 GB, free space – 28 GB (after dropping the table) – Data
file, used space – 3 GB, free space – 300 MB- txn log’, No".

After I had my fill, I went ahead running this command

and bingo, I have had my best shot. The drive space had gained 18.5 GB space
which didn’t happen thro’ dbcc shrinkdatabase. My buddy was elated and asked me
how did I do it? I just had run this command and it worked out, and I was asked
why dbcc shrinkdatabase didn’t work. OK, now it is turn of others to fire
salvo’s at meCrying.

I did little search on my blog roll and found an interesting article by
PSS team member (Bob Dorr – SQL Server Senior Escalation Engineer) – http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-tabular.aspx

Though this article was on a different issue but it revealed few things for me
(an insight) and here is quick stuff I learnt and thought of sharing with you

  • The shrinkdatabase command is based on a percentage
    where as shrinkfile is based on a target.  
  • Shrinkdatabase uses the percentage to calculate a
    target for each file but can’t override the minimum file size.
  • ShrinkFile allows shrinking below the minimum file
  • dbcc shrinkdatabase(‘db name’, tabular) gives you a
    report of a dbcc shrinkdatabase without invoking the shrink actions.
    (report only)

WARNING: As always any undocumented syntax can
change and may not be supported.  This should be used under the guidance
of Microsoft SQL Server Support.

The shrinkdatabase command results
in a loop over each of the files in the database.  Using the current free
space of the file and the percentage the shrink target is calculated for each
file.   If the calculated target is less than the minimum size of the
file the error is produced and the shrink attempt aborted for that file.

Based on the last bunch of lines from the PSS blog, I understand that when dbcc
shrinkdatabase was run the file couldn’t shrink because calculated target was
less than the minimum size of the file
(but returned no errors, and this is
a surprise). I am leaving "why no errors didn’t come up?" to other
experts to explain or maybe I shall discover myself someday.

I hope you guys have had a good reading. Let me know your thoughts!

Test your SQL Server security – SQL Injection! May 5, 2008

Posted by msrviking in Security.
add a comment

Denis, (Oops, did I get his name wrong?) has given link to SQL Server security testing. I haven’t tried all the tests listed in the page but thought I should share it with you all.

Click here to read more,

At a Loss May 5, 2008

Posted by msrviking in DBA Rant.
add a comment

This is how my life goes on too! An interesting article by Sean, and I am a regular reader of his frank blog entries on how a DBA’s life goes on.


Happy reading!