Some of your bad queries are going to be worse for your instance than others.Īnd what’s the most efficient way to fix the top three queries with the least amount of work?Īfter automating your queries, you can now practice: Practice finding the worst queries and diagnosing a solution One note is that the SQL Query Stress tool originally written by Adam Machanic is now maintained on GitHub by Erik, Ejlskov Jensen.Michael J Swart wrote a great post on generating concurrent activity that lists out a bunch of tools which can help.Some options for running a bunch of queries: You’ll learn quickly that you do have to meter them out in a way, because just running a ton of stuff in a tight loop is going to completely overwhelm your CPUs. One easy way to do this is to set up SQL Server Agent jobs that run the queries in a loop or on a scheduled basis. Once you have a bunch of slow queries, you can create an environment of bad queries. Use the queries to make an anti-pattern environment I find that the easiest way to do this is to make lots of notes in my TSQL scripts as I go, to remind myself of the performance at different points in the script. Learn how to measure performance using tools like STATISTICS TIME and STATISTICS IO.Research operators in the execution plans when the query is slow and fast.For each anti pattern you create, understand the execution plan and how to measure the queryįor each slow query you write, test different solutions and compare them. If you really want to learn performance tuning outside of a production environment, writing your own slow code and then speeding it up is the most effective approach. Aaron Bertrand’s Bad Habits Revival on the SQL Sentry blog (not all cause performance issues, you’ll learn as you work through the list).Grant Fritchey’s Seven Sins against TSQL Performance article on Simple Talk.Two articles will get you started on anti-patterns: You wouldn’t think that it takes talent to write truly crappy TSQL, but it takes me quite a long time to write terrible queries that demonstrate an anti-pattern against a sample dataset. The hardest part is going to be writing slow queries properly. The best way to get a job speeding up queries is to write a blog about the queries you’ve sped up. The best way to learn to speed up queries is to write slow ones. You know how people say that the best way to learn something is to teach it? (Switching around between databases in a class can be confusing.) Start writing queries that demonstrate TSQL anti patterns - and make them slow If you’re planning to take your experience and teach a class, you may want to focus on just one sample database, though – and also make sure you have the rights to share it with students. If you have enough space to keep multiple of these databases on your instance, there’s no reason to only use one of them as a learner. There are also many free data downloads at, if you want to build your own!.SQLSkills sample databases, including their Credit database, SalesDB, and a Baseball Stats database.StackOverflow sample database, shared on BitTorrent by Brent Ozar.I’m biased towards this one because I maintain it on GitHub. The BabbyNames sample database - large or small options.Microsoft’s World Wide Importers sample database for SQL Server 2016.Jonathan Kehayias wrote a script to enlarge it. To demonstrate slow queries and speed them up, expanding the database is very helpful.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |