Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The Clonesome Database Expand / Collapse
Author
Message
Posted Friday, March 23, 2012 10:49 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:01 AM
Points: 594, Visits: 2,580
Comments posted to this topic are about the item The Clonesome Database


Best wishes,

Phil Factor
Simple Talk
Post #1272203
Posted Saturday, March 24, 2012 5:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
For those interested further may I suggest reading:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/21/cloning-in-sql-server-2005.aspx


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1272225
Posted Saturday, March 24, 2012 6:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Hi,

there's probably nothing wrong with running (most) DMV scripts on production.

Remember the information largely already exists anyway, you are just harvesting it... Also to set the transaction isolation level to READ UNCOMMITTED so you don't hold or honor locks.

You can discover a lot more about improving SQL performance via DMVs in this recently published book "SQL Server DMVs in Action" (http://www.manning.com/stirk/). It contains more than 100 scripts to identify problems, and offers a wide range of solutions. Nice reviews on Amazon too http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730/

Chapters 1 and 3 can be downloaded for free from http://www.manning.com/stirk/. Chapter 1 includes scripts for:

A simple monitor

Finding your slowest queries

Find your missing indexes

Identifying what SQL is running now

Quickly find a cached plan

Thanks

Ian
Post #1272229
Posted Saturday, March 24, 2012 7:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:01 AM
Points: 594, Visits: 2,580
@bitbucket-25253.
I'd already referenced Kalen's article in the editorial. It was from her that I originally learned about the trick!



Best wishes,

Phil Factor
Simple Talk
Post #1272231
Posted Saturday, March 24, 2012 7:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:01 AM
Points: 594, Visits: 2,580
@ianstirk

Normally, access to a production system is precluded on grounds of compliance regarding access to private data, rather than any possible consequences of running DMVs themselves. I'd agree that they don't leave a footprint. Normally, the statistics blobs have to be scripted off by production staff for the same reason. This is why a PowerShell routine is so handy, since it makes it easier for them, and can be scheduled so that a history can be kept.

Also note that 'Performance Tuning with SQL Server Dynamic Management Views' http://www.sqlservercentral.com/articles/books/70486/ by Tim Ford and Louis Davidson is also packed with such information, and is available from this site as an Eblook entirely free



Best wishes,

Phil Factor
Simple Talk
Post #1272235
Posted Saturday, March 24, 2012 1:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Hi Phil,

Just so readers understand this clearly, the DMVs do not access private data (e.g. customer details), instead they access metadata (e.g. what queries take the longest time to run). So stating the data is private, is not a valid reason to preclude the use of DMVs on a production database.

If you have access to the production server, and if you know the troublesome query, its execution plan (typically) already exists, and can be obtained easily via the DMVs, there is no reason to run it again! If the query is running slowly, do you really want to run it again (slowly).

It is possible to find the state of the statistics (number of rows changed since last stats update, date last updated etc) via a simple query of some system tables, these are not DMVs but can be queried in a similarly (typically) innocuous manner. (It might be argued that the stats column values are ‘reflections’ of private data)

The above suggests there is no reason to leave the production database...

Thanks
Ian
Post #1272268
Posted Monday, March 26, 2012 6:26 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 1,445, Visits: 2,163
As most of you stated, using DMV will be safe on the production server but working what ever the reason on a production server is having a damocles sword up your head.

How many times a "Whoops" query was run unintentionally on a production server?
Who will be blamed first if something wrong occurs while you are working on it even if it's not your fault?

Using the less stressful, the minimum time and a tested & approved method to fetch only the require data on a production server is a safe bet to avoid anything that could go wrong. To my eyes, it is a safe way to do what you need to do.

It's not because you could it means you should...

It is a wonderful think to know to script out that and mimic a server! Thks Phil!
Post #1272587
Posted Monday, March 26, 2012 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Hi Megistral,

I understand your concern, but sometimes, time is of the essence…

Remember you will be running with a transaction isolation level of READ UNCOMMITTED , and you’ll only be SELECTing from a relatively small set of data.

You can also use a restricted non-admin account on your production database, preventing you from doing “Whoops!” queries (You can swap to an admin account when you need to do updates).

And if you use SQL Server 2008 (and above), you can change the status bar color when you are on the production database, this might also help prevent errors. http://connect.microsoft.com/SQLServer/feedback/details/361832/update-status-bar-colour-when-changing-connections

A note about the article, I’ve seen stats exported before, with the purpose of updating certain stats details (e.g. number of rows in table/index), such that when you run a query’s estimated execution plan, you will see the plan for that changed number of rows. It’s quite useful for determining how the query plan changes as the number of rows change (useful for capacity planning?!).

Thanks
Ian
Post #1272652
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse