October 12, 2014 at 10:10 pm
I am trying to do performance tuning on a database that is in production and that I do not have access to. I can be given a backup copy of the database. Now I will be restoring this backup copy on a different server to look at it. My question is: Will all the DMV data that I would need to query, things like checking missing indexes, checking indexes that are not used, top 100 stored procedures that take the most time etc. Will all that info/data come in the .bak that I get from prod or is some of it stored in the msdb or master database and I will not be able to look at as I am not getting backups of msdb and master databases.
Also, If I check the execution plan on the restored database on a similar sql server as the original production server would I be getting a the same kind of results as the production server.Is all the execution plan info carried over in the backup file?
Thanks and appreciate any guidance/tips on this topic.
bharatvip.
October 13, 2014 at 2:31 am
The definition of them is in the system resource database, the data for most is memory-only, no persistence.
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
October 13, 2014 at 5:35 am
Bharatvip (10/12/2014)
I am trying to do performance tuning on a database that is in production and that I do not have access to. I can be given a backup copy of the database. Now I will be restoring this backup copy on a different server to look at it. My question is: Will all the DMV data that I would need to query, things like checking missing indexes, checking indexes that are not used, top 100 stored procedures that take the most time etc. Will all that info/data come in the .bak that I get from prod or is some of it stored in the msdb or master database and I will not be able to look at as I am not getting backups of msdb and master databases.Also, If I check the execution plan on the restored database on a similar sql server as the original production server would I be getting a the same kind of results as the production server.Is all the execution plan info carried over in the backup file?
As Gail mentioned, most DMVs do not persist the data. Which means that some DMVs only display data since the last server restart. Might be useful to know when looking at wait stats or index usage.
Regarding the execution plan: there are some server properties that might affect the outcome of an execution plan, such as the threshold for parallellism.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 10:47 am
Thanks for the replies Gail and Koen!
So from what I understand based on what you have said, all the basic sql scripts to create the DMV's are in the system resource database and will probably be the same in the prod and any other sql server installation as long as it has the same sql server version. The data as you have said does not persist, so the results from these views must get created at execution time.
Question: So barring the few stats at the server level that may not get picked up, is it ok to look for missing indexes, indexes not used, worst performing queries, bad exec plans etc on a restored copy of the production database and can we feel comfortable in applying the results on the production copy? Is this advisable, done in a lot of places?
Thanks,
BVip
October 13, 2014 at 2:28 pm
Bharatvip (10/13/2014)
is it ok to look for missing indexes, indexes not used, worst performing queries, bad exec plans etc on a restored copy of the production database
Um...
Let me quote the pertinent part of my post, in case you missed it.
the data for most is memory-only, no persistence.
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
October 13, 2014 at 3:14 pm
All this is new to me and I was not sure I understood: "the data for most is memory-only, no persistence.".
Does this mean the data needed for the DMV queries I will run on the restored copy of production does not have the data needed (as the data is in memory of the prod server) to run queries and get meaningful results.
Would be very helpful if I could get a 'Yes, results will not be ok' or 'No, results will be ok' answer as I am not too clear on it.
Thanks,
BVip.
October 13, 2014 at 3:38 pm
Bharatvip (10/13/2014)
All this is new to me and I was not sure I understood: "the data for most is memory-only, no persistence.".Does this mean the data needed for the DMV queries I will run on the restored copy of production does not have the data needed (as the data is in memory of the prod server) to run queries and get meaningful results.
Would be very helpful if I could get a 'Yes, results will not be ok' or 'No, results will be ok' answer as I am not too clear on it.
Thanks,
BVip.
Results will NOT be OK. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2014 at 3:41 pm
Thanks Koen!
October 14, 2014 at 2:31 am
Bharatvip (10/13/2014)
Does this mean the data needed for the DMV queries I will run on the restored copy of production does not have the data needed (as the data is in memory of the prod server)
That is exactly what it means.
The data that the DMVs display is memory-only (prod server memory in this case). Since a backup contains the persisted data in a database (which the DMVs are NOT), a restored copy of a database backup will not have any of the DMV 'contents' from the source 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
October 14, 2014 at 10:17 am
Thanks for the clarification Gail, appreciate your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply