The Clonesome Database

Question

Post reply

The Clonesome Database

Phil Factor

SSC-Insane

Points: 20244
More actions
March 23, 2012 at 10:49 pm

#258236

Comments posted to this topic are about the item The Clonesome Database
Best wishes,
Phil Factor

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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[/url]
Before posting a performance problem please read[/url]

ianstirk Ten Centuries Points: 1310 More actions · Answer 2

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 SSC-Insane Points: 20244 More actions · Answer 3

@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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 4

@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

ianstirk Ten Centuries Points: 1310 More actions · Answer 5

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 SSCrazy Eights Points: 8787 More actions · Answer 6

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 Ten Centuries Points: 1310 More actions · Answer 7

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