Tuesday, November 3, 2009

Performance hit for SELECT because of fragmentation?

SQL PASS - I got help from an expert on SQL PASS, Seattle

Q: Is there a performance hit on the "select"-side because of some fragmentation of the data base files caused by auto growth?

A: No. But you might want to enable "Perform volume maintenance tasks"

Question details
We have a generic script-based deployment of many data bases, and therefore have auto-growth on them, so they are created in exactly the same way. There are a total of ten data bases with twelve files each, so 120 files. The files all start at 100MB and grow by 25%. To give each data base special treatment makes changes more expensive (We charge by the hour).

Solution details
1) Keep scripts as is.
2) Enable "Instant Initialization"
a) open a command window and run "sc qc ", for me it is "sc qc sqlserver$datawarehouse"
b) note the user name from "SERVICE_START_NAME" attribute.
c) Fire up ocal policy editor, start -> run -> gpedit.msc
d) navigate to "Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignments", open "Perform volume maintenance tasks"
e) add the user from step b)
f) restart your sql server instance (not sure if this is needed, but seems a good idea)

and thats all,have a nice day

btw:I will not mention the name of the expert that helped me, because the person can not make public advice because of legal reasons. And things might have been distorted by me translating the advice into this post.

No comments:

Post a Comment