February 5, 2016 at 8:31 am
Hi all!
A developer came to me today with a problem they want me to solve...
they have a server where they try their new code and apparently when they do that they drop all the databases and then recreate them from TFS
this step, the create takes a long time on a specific database and we think it might be because its partitioned, its got about 20 partitions and each have its own filegroup.
Instant file initialization is on and i changed the model db to be 1gb in size and have a logdisk that is 1gb too.
still, creating this database and creating about 200 tables on it takes about 15 minutes!
one of the devs tried it on his local computer with a ramdrive, just to exclude the virtualization layer or the SAN having a bad day and it still took about 13 minutes!
any ideas on why its so slow?
February 5, 2016 at 9:14 am
Does the database have a huge transaction log file? Instant file initialization applies to data files only; log files always have to be zeroed.
Does the create database script from TFS pre-populate some of the tables with so-called static data? If so, how much?
February 5, 2016 at 9:28 am
February 5, 2016 at 3:06 pm
jCoke (2/5/2016)
any ideas on why its so slow?
This issue is so easy to trace. At least to pinpoint the specific query(s) that takes most of the time. Are you familiar with SQL Server Profiler?
February 8, 2016 at 12:09 am
Yes, i am somewhat familiar with profiler, just dont know what to trace?
February 8, 2016 at 8:47 am
If you are trying to identify the longest running process, on a 2014 system, I wouldn't use Profiler (I wouldn't ever use Profiler to capture data, just consume already captured data), or Trace. Instead, use extended events. If you're attempting to identify the individual statement that is causing problems, then capture the sql_statement_completed and/or the rpc_statement_completed events.
However, I'd focus where Hugo suggested earlier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2016 at 4:42 pm
jCoke (2/8/2016)
Yes, i am somewhat familiar with profiler, just dont know what to trace?
Just use default template and put filters on duration and database name, and save result into a table. Then analyze what query(s) took the most of the time.
I honestly don't see a big difference between Profiler or extended events for such a task. It's a dev\qa environment, so who cares about some performance impact, but Profiler is much easier to configure especially for someone with little or no experience.
February 9, 2016 at 5:12 pm
Alexander Suprun (2/9/2016)
jCoke (2/8/2016)
Yes, i am somewhat familiar with profiler, just dont know what to trace?Just use default template and put filters on duration and database name, and save result into a table. Then analyze what query(s) took the most of the time.
I honestly don't see a big difference between Profiler or extended events for such a task. It's a dev\qa environment, so who cares about some performance impact, but Profiler is much easier to configure especially for someone with little or no experience.
True.
Profiler is however on the deprecation list. Absolutely no time like the present to start learning Extended Events, which performs better and does more anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply