Everybody tells you to capture baselines – but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources?
All you have to do is install the free, open source sp_BlitzFirst and sp_BlitzCache from our First Responder Kit, create a database to hold your results (I like to call mine DBAtools), and then set up an Agent job to run it every 15 minutes:
|
1 2 3 4 5 6 7 8 |
EXEC sp_BlitzFirst @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirst', @OutputTableNameFileStats = 'BlitzFirst_FileStats', @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats', @OutputTableNameWaitStats = 'BlitzFirst_WaitStats', @OutputTableNameBlitzCache = 'BlitzCache'; |
I show you how to do it and query the baseline data in today’s webcast:
15 Comments. Leave new
Ummmm, Brett… this really doesn’t work for SQL servers with a set of Trend Micro databases. Trend Micro overwhelms the results with useless crap that I can’t seem to filter out, no matter how I try. Too bad for me, I guess.
Can you be more specific about what kinds of data you can’t rule out?
If I am logging this to tables the way you show does it automatically pull the expert mode data set?
Keith – yep, as long as you use the instructions in the post.
hi Brent,
Does this work with Azure SQL DB?
no, there is no SQL Agent, but Sp_BlitzFirst should work, so if I create a RunBook, will it work?
Henrik – great news, yes! sp_BlitzFirst, sp_BlitzCache, sp_BlitzIndex, and sp_BlitzWho all work in Azure SQL DB and Hyperscale. (It’s been about 2-3 months since I’ve tested them in Managed Instances, but they did work at the time.)
sp_Blitz does not, but there’s an open Github issue for that here if anyone wants to tackle it (I don’t, hahaha): https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1970
If I want to pull all the data into a central repository using linked servers, how do I get around the issue of the xml datatype? I can’t query that across a linked server? The only way I know of is to create views on the tables, leaving out the xml columns. Also can the Power BI script be used against a centralized database?
Dennis – you’ll either need to exclude the XML data types, or cast them as nvarchar, or use a more ETL-focused tool like SSIS. About Power BI – yep!
Hey Brent!
I have a SQL Server 2014 SP2 CU4 server with 48 cores and 1 TB of Ram.. sp_Blitzfirst has been running for 15 minutes… Is there something I need to do to get that fast return time?
Thanks!
Oh, and forgot to mention…
It’s stuck at “Beginning investigatory queries”
sp_BlitzFirst version with the line info is:
SELECT @Version = ‘7.91’, @VersionDate = ‘20191202’;
Adam – for support, check the readme.
Brent is there any way to put the output of running processes, as is seen when running manually, into an output table? Currently I am only seeing the capability to store stats for filestats, perfmon stats, wait stats, cache stats but not anything for the processes (queries) that are running at the start and end of the run.
Many thanks.
Yep! That’s the @OutputTableNameBlitzWho parameter.
Hi Brent , Do i need to create the database and all tables with column names manually to gather and save the info over time through SQL Job Agent (sp_BlitzFirst) ?
The database, yes. The tables, no. Also, make sure to read the documentation. Cheers!