[MSSQL] 안보고 그냥 스크립팅 ~ (SQL Server Takeover Script)

sp_BLITZ™ – SQL Server Takeover Script

When people hand us a SQL Server and ask us to take it over, we need to do a fast health check.  We need to put on our latex gloves, do a little poking around, and discover the dirty secrets before they catch us by surprise.  That’s where our sp_Blitz™ script comes in: it helps you rapidly assess configuration, security, health, and performance issues.  We organize the results in a prioritized list and give you simple explanations of each risk, plus a column of links that you can copy/paste into your browser for more information about each issue.

Download the sp_Blitz™ code here, and then you can copy/paste it into SSMS.  Run it to create the stored procedure, and then just EXEC sp_Blitz to run the checks.  On larger servers, it may take a couple of minutes for some of the more detailed tests, like untrusted foreign keys, but this shouldn’t cause any blocking.

Here’s a 4-minute video explaining how to use it:

WHAT THE PARAMETERS DO

@CheckUserDatabaseObjects – if 1, we review the user databases looking for things like heaps and untrusted foreign keys.  If your databases have more than a few thousand objects, this may require additional processing time.

@CheckProcedureCache – if 1, we grab the top 20-50 resource-intensive plans from the cache and analyze them for common design issues.  We’re looking for missing indexes, implicit conversions, user-defined functions, and more.  This fast scan isn’t incredibly detailed – we’re just looking for queries that might surprise you and require some performance tuning.

@OutputProcedureCache – if 1, we output a second result set that includes the queries, plans, and metrics we analyzed.  You can do your own analysis on these queries too looking for more problems.

@CheckProcedureCacheFilter – can be CPU, Reads, Duration, ExecCount, or null.  If you specify one, we’ll focus the analysis on those types of resource-intensive queries (like the top 20 by CPU use.)  If you don’t, we analyze the top 20 for all four (CPU, logical reads, total runtime, and execution count).  Typically we find that it’s not 80 different queries – it’s usually 25-40 queries that dominate all of the metrics.

@OutputType – if ‘TABLE’, we dump out the findings in, well, a table.  If ‘COUNT’, a vampire comes to your door and – wait, I’m hearing that’s not quite right. If ‘COUNT’, we output a single result row with the number of problems found.  You can use this in conjunction with monitoring software to find out when somebody’s been sleeping in your bed.

MORE FUN LINKS

• Download the sp_Blitz™ code
• Download it as a SQL Server Management Studio custom report
• Report bugs, request improvements, or send code you’d like to add by contacting us
• Not sure what you’re looking at, or think you’ve taken over a server full of land mines? We offer fast SQL Server health checkups. To learn more, contact us.

출처 : http://www.brentozar.com/blitz/