Mike McMillan

When we started to look into upgrading, we already had SQLServer 2017 installed, but the databases were at compatibility 100 (2008). Using the compatibility mode option is a great way of divorcing the version of SQLServer from that of your databases, allowing you to keep the version of SQLServer up-to-date without worrying about breaking your application. Our testing process for upgrading the databases was to: Clone the production databases into the sandbox cluster Re-configure the sandbox application to target these new databases Use cypress.io to load test the application Take a SQL trace whilst load testing Increase the database compatibility option from 100 to 140 Re-run the load tests and SQL trace Load both traces into Qure profiler to analyse the differences. It is expected that some queries will perform better, and some might be considerably worse, so this testing is important to ensure we don’t take down the production application! We also invited our QA test eng...

So I’ve just watched Brent’s How I Use the First Responder Kit: sp Blitz and figured I’d write some notes on what I found interesting, for the sake of my future self. Create a new database Blitz_Maintenance on the same server that you wish to analyse and install the SQL-Server-First-Responder-Kit . Brent installs his scripts into the master database, but I didn’t fancy playing with system databases so opted to create my own. Run sp_blitz, which will scan every database in the sql instance. sp_blitz @CheckServerInfo = 1 , @OutputDatabaseName = 'Blitz' , @OutputSchemaName = 'dbo' , @OutputTableName = 'Blitz_Results' The latter 3 parameters are optional to output the data into a new database. If you don’t have access to do this, just omit those parameters and copy the data from the query results panel in SSMS. Have a read over the set of data that is produced, but for an overview on the server take a look at the Priority=250 entries. It wi...

I don’t know if you’ve heard of Brent Ozar before, but he’s a chap that works on Making Microsoft SQL Server go faster . He’s the author of the highly recognised SQL-Server-First-Responder-Kit repository. He’s got a bunch of training material which is worth a look at, and some of his sp_blitz modules are free right now. I’ll be working through some of this training material and posting things I find interesting here.