A gigantic script that gives you a lot information to view and review current databases and SQL Server settings, options and configurations. I see it becoming very favorite of many operational DBA folks. I’d recommend it run it at least once in a month or anytime you want to have very detailed information for any known/unknown server.
Sometimes the output of this is so overwhelming and can take hours/days to go over on each of its section and making sure that your SQL Server is configure as per industry best practices. Again, it is a BIG YES for anyone who runs it often and understand what to look for.
What it will do for you?
The output will consist of more than 15+ output windows with tons of information about you all the database on the instance. It will Check and show you most common skewed Best Practices and performance issues. It is compatible from SQL Server 2005 onwards. By default, to simplify output and not to run IO intensive (fragmentation etc.) checks we can use optional parameter to narrow these checks to specific databases.
- Processor (NUMA, MAXDOP etc.)
- Memory
- Page file I/O (I/O Stall subsection (wait for 5s) *)
- Server (PP, Disk and NIC Bindings)
- Service Accounts
- Instance (trace flags, deprecated and discontinued , black box trace etc)
- Database and tempDB
- Performance
- Indexes and Statistics
- Naming Convention
- Security
- Maintenance and Monitoring
Run the script with these changes so script would not spend much on things you do not want to know and keep execution time acceptable.
SET @duration = 90
SET @ptochecks = 1 –(1 = ON; 0 = OFF)
SET @allow_xpcmdshell = 1 –(1 = ON; 0 = OFF)
SET @spn_check = 0 –(1 = ON; 0 = OFF)
SET @diskfrag = 1 –(1 = ON; 0 = OFF)
SET @ixfrag = 0 –(1 = ON; 0 = OFF) –Keep it off could take really hours on VLDBs
SET @ixfragscanmode = ‘LIMITED’ –(Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, SET @logdetail = 0 –(1 = ON; 0 = OFF) –keep it off to avoid time spending on noise
SET @bpool_consumer = 1 –(1 = ON; 0 = OFF)
SET @gen_scripts = 0 –(1 = ON; 0 = OFF)
SET @dbScope = NULL –(NULL = All DBs) –unless you want to check a particular db.
PRE-REQUISITES
Only a sysadmin/local host admin will be able to perform all checks.
- If you want to perform all checks under non-sysadmin credentials, then that login must be:
- Member of serveradmin server role or have the ALTER SETTINGS server permission;
- Powershell must be installed to run checks that access disk configurations, as well as allow execution of unsigned scripts.
Thanks to tigertoolbox and also you can download updated script from here https://github.com/Microsoft/tigertoolbox/tree/master/BPCheck
Thanks Tiger tool belt
adrshen 