The Clonesome Database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » The Clonesome Database

The Clonesome Database

Rate Topic

Display Mode

Topic Options

Author

Message

Phil Factor

Posted Friday, March 23, 2012 10:49 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

Comments posted to this topic are about the item The Clonesome Database

Best wishes,

Phil Factor
Simple Talk

Post #1272203

bitbucket-25253

Posted Saturday, March 24, 2012 5:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 5,101, Visits: 20,201

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

ianstirk

Posted Saturday, March 24, 2012 6:46 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:22 AM
Points: 47, Visits: 762

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

Phil Factor

Posted Saturday, March 24, 2012 7:35 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

@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

Phil Factor

Posted Saturday, March 24, 2012 7:58 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

@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

ianstirk

Posted Saturday, March 24, 2012 1:04 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:22 AM
Points: 47, Visits: 762

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

Megistal

Posted Monday, March 26, 2012 6:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:35 AM
Points: 1,243, Visits: 1,776

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

ianstirk

Posted Monday, March 26, 2012 7:31 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:22 AM
Points: 47, Visits: 762

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 »

Permissions