SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Clonesome Database


The Clonesome Database

Author
Message
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2141 Visits: 2972
Comments posted to this topic are about the item The Clonesome Database


Best wishes,

Phil Factor
Simple Talk
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8089 Visits: 25280
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
ianstirk
ianstirk
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 1037
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
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2141 Visits: 2972
@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
Phil Factor
Phil Factor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2141 Visits: 2972
@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
ianstirk
ianstirk
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 1037
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
Megistal
Megistal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 2555
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!
ianstirk
ianstirk
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 1037
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search