In my previous blog i shared my experience on my first day of work at a new client (https://wordpress.com/read/feeds/90430081/posts/2132980200).
After this initial day, time to get to know the sql estate at hand.
Accounts were already taken care of, and i got the admin level. I can check whatever i want and it shows a level of trust from the client. Giving the new guy control of your production database is not taken lightly.
To get to know the estate, i could have used scripts, like the first responder kit from @brentO. But, to do that i need to install things on the servers (six in total). I didn’t feel comfortable doing that at that point in time. So i decided to run a few of my own basic scripts to see what i’m dealing with. The things i check:
- Version and edition of SQL Server
- Memory of the server and availability for SQL Server
- CPU
- Cost Threshold for Paralellism
- Max degree of Paralellism
- Number of TempDB’s (also in regard of the max degree of paralellism)
- Size of TempDB (are they in line or is there one big file and a few smaller ones)
- Growth setting for TempDB
- Number of databases
- Size of databases
- Usage of databases (not just development or production but also if they contain e.g. sales data or personal data)
- Disk latency
- Wait statistics
- Number of indexes on a database
- Index usage
- Fill factor of the indexes
- Last index rebuild or reorganize
- last statistics rebuild
- last succesfull backup
- last DBCC checkDB
- Active traceflags
For me, this gives me some insight into the technical side of the servers. But what i’m missing here is user experience. What problems are the users having. What are the things users want to change. I’m missing the human factor here.
That’s where my ‘liaison’ comes in, the on-site sys admin who briefs me about the issues users have and feeds me with things that have to be improved.
First thing i did was to ask the sys admin to move the TempDB from a slow server to fast storage. Besides the move i made sure the files were equal in size and growth factor and the number of files equal to the max degree of paralellism. After two days came the result from the users. They are happier because their applications run faster.
I’m far from reaching my target, but now i’ve got some credit with both users and sys admins that i might be usefull. Now on to the next steps. Small iterations of improvement, some may lead to performance degradation, but “He who dares, wins”
Reitse's blog 