Performance tuning with SQL Server

Question

Hi, I was trying to contact the author of a book I am reading on SQL Server query performance, but it seems the e-mail address provided in the book does not exis any more. So I decided to ask the community. I am pasting the messasge I had written below. Thanks in advance.

======

I have bought your book (SQL Server 2008 Query Performance Tuning Distilled) and got to know that my SQL Server Express Edition won't support many of the important tools I will need in order to run the necessary performance tests. As you have said, it is important to have an initial analisys to check how much queries are hurting database performance. Since I don't have profiler, I cannot know. I have downloaded a free project tool, but I am afraid I am not getting the information I need. And it seems to me, the information that should be shown when using SET STATISTICS won't appear in SQL Server Express either.

Which is the lowest version of SQL Server that will offer me the tools to run the tests suggested in the book? Is it Developer Edition? Does it offer everything I need in order to follow the examples?

Also, is there any problem to install the new version (like Developer Edition) on top of Express Edition? Or do I have to uninstall the previous version?

I hope I am not bothering much. I would also like to congratulate you on the great book on such an important topic.

Answer 1

Partial answer to your issues

SQL Server Developer edition is the equivalent of Enterprise edition but with only development license.

You can install the Express edition side by side with Developer/Enterprise edition. You will not haveto unistall the Express edition.

Answer 2

You can buy Developer Edition for $49, and this will give you tools like the full SSMS (Management Studio), profiler, etc. You can install this on top of Express (you can have multiple instances of SQL Server on the same machine), but if you are only supporting Express in production, I would just install the client tools and not bother with installing a new instance of SQL Server that happens to support features you won't be able to actually use when you port your code.

However, a lot of the analysis you can do on troubleshooting performance can be done without profiler, and without a full version of SSMS. You can download SSMSE (the express version of Management Studio) here, and from there you can run queries against DMVs, inspect query plans, and start server-side traces. Server-side traces are preferred over using the profiler UI anyway. A server-side trace can log all of the performance metrics you've read about in the book to a file or table, and then there are functions that allow easy consumption of that data.

In fact you can even do many of these things without a UI at all, but to start it would probably be best to use a query window, so I would download Management Studio Express and poke around a bit.

PS I let Grant know you were asking.

Answer 3

I know that another MVP sent this thread to Grant Fritchey, but in the mean time, some comments on what you've said in your post.

First SQL Express supports SQL Trace which is what Profiler runs on top of. You can manually define a trace and still collect the information needed for tuning, it just takes more work. SET STATISTICS also works in Express just as it does in Standard or Enterprise Edition. If you have an example where it doesn't it would be new to me.

For your Developer Edition Question, yes they can be installed side by side in different named instances. However, you can only use Developer Edition for Development, it can't be used for serving data in a production application, and you have to be careful when you are targeting Express Edition for final deployment to only use features in Express Edition since Developer has all of the Enterprise Edition features.

You should be able to do the same level of tuning on Express Edition only, it just takes a bit more work. If you bought a Developer Edition license, you really would only need the Workstation Tools installed, not the actual database engine. Profiler and such are tools in the Workstation Tools, not part of the engine itself.

Answer 4

Profiler is nice to use, but the explain plan is more accessible. The explain plan is also common across other databases - Oracle, MySQL, etc. Don't get bogged down in using a specific tool.

The Developer Edition is the Enterprise Edition - the difference is the license doesn't allow you to use the Developer Edition for commercial use.

Yes, you can install SQL Server editions on the same workstation - side by side if you like. It's for things like migrating to other editions & versions of SQL Server. Unless you're using both instances, I don't recommend doing this for the fact that the unused instance will be using system resources better served elsewhere.

Answer 5

Aaron has already answered this question as well as I can, probably better.

I can provide you with one other bit of information. The email from the book should be grantedd -at- gmail.com. Unobfuscate as needed.

I would have replied as a comment, but I'm not on SO much, so I have no rep here at all.

Answer 6

Another good link that i would like to share is : Tips for improving and tuning sql server database