August 3, 2010 at 1:27 pm
I will be using the attached script to make decision . In this test i only need to decide whether moving tempdb data file to another drive has it improved the performance or not. I am not monitoring tempdb space or anything else. Mentioned below is my stratergy, please advice if this is right or if i need to make changes.
i) after i split the file if the test job finishes i can say moving the file has improved the performance.
ii) also i will monitor the counters from io_stall_read_ms and io_stall_write_ms before and after splitting the file..lower values will indicate increase in performance. I will be using the following script.
thanks...
August 4, 2010 at 3:51 am
Spliting file definitely helps you. but the question what makes you to decide this spliting ?
What kind of issues you are facing on tempdb side ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 4, 2010 at 4:40 am
iqtedar (8/3/2010)
In this test i only need to decide whether moving tempdb data file to another drive has it improved the performance or not.
Bhuvnesh (8/4/2010)
Spliting file definitely helps you.
Not necessarily. It depends on if this other drive is only a logical drive or if it's actually a different physical drive.
Remember, you can have multiple logical drives on the same physical disk and, if that disk only uses 1 disk controller, you'll still have the same performance as if you left the file on the same initial drive.
I strongly advise getting together with your server admin to verify the drive setups to make sure you are indeed using different physical drives with different disk controllers. Otherwise, your test will be fairly worthless and a waste of your time.
August 4, 2010 at 6:24 am
Brandie Tarvin (8/4/2010)
Not necessarily. It depends on if this other drive is only a logical drive or if it's actually a different physical drive.
yes..true ..i was also talking about physical seperate disks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 4, 2010 at 6:49 am
A note of interest. One of our more SAN-Experienced DBAs told me a few months ago that a SAN might also only have one controller, no matter how many physical disks it has. So that's another thing to keep in mind when separating files.
August 4, 2010 at 7:54 am
thanks...did anyone look at the script and see if it helps? many of our processes are highly dependent on tempdb and currently we have temdb data and log file on the same drive.
August 4, 2010 at 10:54 am
iqtedar (8/4/2010)
did anyone look at the script and see if it helps?
No. Partially because you didn't include create scripts for your tables so that I could actually test it. @=)
But I will say one thing, Never, Ever, Ever use keywords for aliases. It will bite you in the rear when you least expect it.
August 4, 2010 at 11:03 am
I don't understand why you are inserting into your table, then deleting, and then inserting again.
It seems you should eliminate the delete and last insert. Historical numbers make more sense to me. You can trend and track that way.
Also, You really should specify the columns in your insert statement. Not specifying your columns really can come up and cause problems in the future.
Otherwise, it seems like you should be able to use this script to monitor.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2010 at 11:04 am
Got it. Attaching create table scripts.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply