January 23, 2013 at 4:05 pm
Hi Folks,
I guess I should say slower than usual.
I am running a vendor provided import script. It generates individual INSERT statements.
On my production server I am getting hundreds of inserts per second. In my test environment I am getting 1 insert every 1 or 2 seconds. Granted my test server is running the database and the application but I still don't think that would explain this situation.
See the snippet of profiler output below showing the start time column.
The destination table is truncated, has no indexes and should contain about 8000 records when done.
My question: Can I glean any information from profiler start time to tell me whether SQL server is slow committing the transactions or is it that the vendors process is slow in delivering the insert statements?
INSERT INTO....,2013-01-23 15:38:55.470
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:38:55.983
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,2013-01-23 15:38:55.983
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:38:56.983
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,2013-01-23 15:38:56.983
INSERT INTO.... ,2013-01-23 15:38:57.530
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:38:57.983
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,2013-01-23 15:38:57.983
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:38:58.983
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,2013-01-23 15:38:58.983
INSERT INTO.... ,2013-01-23 15:38:59.580
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:38:59.983
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,2013-01-23 15:38:59.983
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:39:00.983
SET TRANSACTION ISOLATION LEVEL READ COMMITTED,2013-01-23 15:39:00.983
INSERT INTO.... ,2013-01-23 15:39:01.657
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED,2013-01-23 15:39:01.983
January 23, 2013 at 5:16 pm
Chrissy321 (1/23/2013)
On my production server I am getting hundreds of inserts per second. In my test environment I am getting 1 insert every 1 or 2 seconds. Granted my test server is running the database and the application but I still don't think that would explain this situation.
Yes, it may explain it.
Have you checked for bottlenecks on test server? at host/OS level?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 23, 2013 at 5:18 pm
Start time will help you determine when it was sent from the vendor app. What you want to do is look at duration to determine if it's your box that's slow in getting the insert done.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 23, 2013 at 5:18 pm
How beefy is the test server? Different hardware, different resources, different results.
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
January 23, 2013 at 5:29 pm
I'll explore the test/prod server differences, the differences are substantial.
Duration is always 0 in profiler which may indicate SQL Server not being fed by the software at the normal production rate.
One nice thing about the vendors product is you can place a switch on the process to populate the import tables but not the final destination tables, so theoretically I can 'test' in production.
Sounds like my weekend plans are firming up! Thanks...
January 23, 2013 at 5:36 pm
Chrissy321 (1/23/2013)
I'll explore the test/prod server differences, the differences are substantial.
(substantial platform differences) = (substantial performance differences)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 23, 2013 at 5:49 pm
PaulB-TheOneAndOnly (1/23/2013)
Chrissy321 (1/23/2013)
I'll explore the test/prod server differences, the differences are substantial.(substantial platform differences) = (substantial performance differences)
+10
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
January 25, 2013 at 4:57 pm
>>(substantial platform differences) = (substantial performance differences)
That was exactly it...and not in a linear fashion either.
Thanks to you folks for educating me about REBAR in the first place. It gives me a very nice pedestal to lecture my vendor from...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply