July 24, 2017 at 2:50 am
Hi all,
I have an odd situation and want to check with you guys that i'm on the right track. I'm the MI and Reporting Developer in my organisation, we do not have a DBA, so i'm the probably the one with the most SQL knowledge.
Our IT team want to change the server our main databases are on and have commissioned a new server with a few more horses and have asked me to test the box/instance to make sure it's up to spec and working.
So i have come up with the below tests but wanted to check that i'm not missing anything major.
Test Case 1; DBCC CheckDB. -- Tracking how long it takes vs a full restore of our live DB to our reporting server.
Test Case 2; Run one of wildly used views with so pre-selected parameters GO 1000.
Test Case 3; Another view that is used alot GO 1000.
Test Case 4; DBCC FREEPROCCACHE --> DBCC DROPCLEANBUFFERS --> Update Stats on 3 of the biggest tables.
Test Case 5; Create a Non-Clusted Index on the largest table.
Anyone have further suggestions as i pretty sure i'm either light on the tests or have been think too narrow.
July 24, 2017 at 3:25 am
Run Glenn Berry's SQL Server Diagnostic Information Queries
😎
July 24, 2017 at 3:36 am
Cheers dude. I will go through them today and report back.
July 24, 2017 at 5:22 am
Most of those tests are IO-bound, not testing anything else.
Capture a workload from your current server using server-side traces. Run it against the new server using Distributed Replay. Capture performance statistics while the workload is being replayed, compare them to your current server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2017 at 6:40 am
Hi Gail,
Appreciate the response as i caught you at SQLBits this year. It looks like i would need to reconfigure my SQL Instance on our live environment to enable the SQL trace tool (amending the new one wouldn't be an issue as it's not live yet), would that require a SQL Agent/Services restart?
If so... I doubt i would be able to do it. I realise that it sounds stupid given the task at hand but there is a significant amount of fear that if we take Live down it will not come up in addition to the disruption to client services.
July 24, 2017 at 6:45 am
Err, no. There's not reconfiguration required to run a server-side trace.
You might need another server (little server, nothing powerful) with the Distributed Replay tools on it to replay the trace, but you don't need to do anything odd with the production server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2017 at 6:59 am
Ah ok. I think i'm with you. So create the Trace file with the Server Side Trace, then use the replay tools to run the trace against the new server?
I thought i was going to have install the SQL Server Replay Client and Controller on Live.
July 24, 2017 at 7:09 am
Yup.
https://blogs.msdn.microsoft.com/mspfe/2012/11/08/using-distributed-replay-to-load-test-your-sql-serverpart-1/
https://blogs.msdn.microsoft.com/mspfe/2012/11/14/using-distributed-replay-to-load-test-your-sql-serverpart-2/
Just don't use Profiler to run the trace. Generate the script from Profiler, and run the script to generate a server-side trace.
Profiler kills servers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2017 at 9:39 am
Honestly your testing is will provide TRIVIAL benefits compared to the HUGELY more important issue here: your admission that you have no DBA. Thus you will ABSOLUTELY mis/suboptimally configure most if not everything related to your SQL Server. That will carry a wide range of ramifications up to and including the potential loss of your company should you suffer a corruption or catastrophic system failure (and yes, I know of multiple situations where that has happened).
I STRONGLY urge you to hire a qualified consultant for maybe 10-20 hours of assistance to get your environment properly configured AND to assist you with understanding your HA/DR needs and getting those handled as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply