Sql Edition Selection: cpu

Showing posts with label cpu. Show all posts

Friday, February 24, 2012

elapsed time vs cpu time

Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
read-ahead reads 0.
Table 'vgn_version'. Scan count 8, logical reads 875, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 571 ms, elapsed time = 17560 ms.
When i run statistics io , and statistics time, on a query (query not
mentioned here), i got above mentioned results.
From the info given above, I see there are no physical reads, so all data is
there in the memory. I am fuzzled with the cpu time and elapsed time values.
When cpu has taken half a second to process the data, why elapsed time is
17.5 sec. I was monitoring cpu when the query being executed, cpu
utilization less than 5% of processor time. So can i derive from this that
remaining 17 sec has been caused to move data between dynmic memory and cpu
memory cache. Or are there some thing i am missing here. Can you please
bring your ideas and let me know if you need any more info.
I have run the query in completely quite machine after reboot.
Thanks,
Subbu.subbu wrote:
> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> 0, read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> reads 0, read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> 0, read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 571 ms, elapsed time = 17560 ms.
> When i run statistics io , and statistics time, on a query (query not
> mentioned here), i got above mentioned results.
> From the info given above, I see there are no physical reads, so all
> data is there in the memory. I am fuzzled with the cpu time and
> elapsed time values. When cpu has taken half a second to process the
> data, why elapsed time is
> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> utilization less than 5% of processor time. So can i derive from this
> that remaining 17 sec has been caused to move data between dynmic
> memory and cpu memory cache. Or are there some thing i am missing
> here. Can you please bring your ideas and let me know if you need any
> more info.
> I have run the query in completely quite machine after reboot.
> Thanks,
> Subbu.
How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL statement includes the time to tech all the
result set pages. Either there were a lot of rows generated and they
took time to fetch or the client application fetched rows slowly.
--
David Gugick - SQL Server MVP
Quest Software|||... or there was some blocking going on...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
>> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
>> read-ahead reads 0.
>> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
>> 0, read-ahead reads 0.
>> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
>> reads 0, read-ahead reads 0.
>> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
>> 0, read-ahead reads 0.
>> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
>> read-ahead reads 0.
>> SQL Server Execution Times:
>> CPU time = 571 ms, elapsed time = 17560 ms.
>> When i run statistics io , and statistics time, on a query (query not
>> mentioned here), i got above mentioned results.
>> From the info given above, I see there are no physical reads, so all
>> data is there in the memory. I am fuzzled with the cpu time and
>> elapsed time values. When cpu has taken half a second to process the
>> data, why elapsed time is
>> 17.5 sec. I was monitoring cpu when the query being executed, cpu
>> utilization less than 5% of processor time. So can i derive from this
>> that remaining 17 sec has been caused to move data between dynmic
>> memory and cpu memory cache. Or are there some thing i am missing
>> here. Can you please bring your ideas and let me know if you need any
>> more info.
>> I have run the query in completely quite machine after reboot.
>> Thanks,
>> Subbu.
> How many rows were returned? Where did you run the SQL Statement from? The total time for a SQL
> statement includes the time to tech all the result set pages. Either there were a lot of rows
> generated and they took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||No blocking at all. This is the only query running at that time
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ufcrY1rTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> ... or there was some blocking going on...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> > subbu wrote:
> >> Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> >> read-ahead reads 0.
> >> Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> >> 0, read-ahead reads 0.
> >> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> >> reads 0, read-ahead reads 0.
> >> Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> >> 0, read-ahead reads 0.
> >> Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> >> read-ahead reads 0.
> >>
> >> SQL Server Execution Times:
> >> CPU time = 571 ms, elapsed time = 17560 ms.
> >>
> >> When i run statistics io , and statistics time, on a query (query not
> >> mentioned here), i got above mentioned results.
> >>
> >> From the info given above, I see there are no physical reads, so all
> >> data is there in the memory. I am fuzzled with the cpu time and
> >> elapsed time values. When cpu has taken half a second to process the
> >> data, why elapsed time is
> >> 17.5 sec. I was monitoring cpu when the query being executed, cpu
> >> utilization less than 5% of processor time. So can i derive from this
> >> that remaining 17 sec has been caused to move data between dynmic
> >> memory and cpu memory cache. Or are there some thing i am missing
> >> here. Can you please bring your ideas and let me know if you need any
> >> more info.
> >>
> >> I have run the query in completely quite machine after reboot.
> >>
> >> Thanks,
> >> Subbu.
> >
> > How many rows were returned? Where did you run the SQL Statement from?
The total time for a SQL
> > statement includes the time to tech all the result set pages. Either
there were a lot of rows
> > generated and they took time to fetch or the client application fetched
rows slowly.
> >
> > --
> > David Gugick - SQL Server MVP
> > Quest Software
> >
>|||Two (2) rows returned.
I run sql stmt in query analyser on the machine where sql server installed,
but connected to server machine using Terminal Services (RDP).
I have run same query on another machine, which is in the same place like
previous one did run the query in similar way, it returned results
differently. Elapsed time is almost matching with CPU. Here also 2 rows
returned.
Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
read-ahead reads 0.
Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
read-ahead reads 0.
Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
read-ahead reads 0.
Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 167 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 171 ms.
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 173 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Thanks,
Subbu.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> subbu wrote:
> > Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> > read-ahead reads 0.
> > Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> > 0, read-ahead reads 0.
> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> > reads 0, read-ahead reads 0.
> > Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> > 0, read-ahead reads 0.
> > Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> > read-ahead reads 0.
> >
> > SQL Server Execution Times:
> > CPU time = 571 ms, elapsed time = 17560 ms.
> >
> > When i run statistics io , and statistics time, on a query (query not
> > mentioned here), i got above mentioned results.
> >
> > From the info given above, I see there are no physical reads, so all
> > data is there in the memory. I am fuzzled with the cpu time and
> > elapsed time values. When cpu has taken half a second to process the
> > data, why elapsed time is
> > 17.5 sec. I was monitoring cpu when the query being executed, cpu
> > utilization less than 5% of processor time. So can i derive from this
> > that remaining 17 sec has been caused to move data between dynmic
> > memory and cpu memory cache. Or are there some thing i am missing
> > here. Can you please bring your ideas and let me know if you need any
> > more info.
> >
> > I have run the query in completely quite machine after reboot.
> >
> > Thanks,
> > Subbu.
> How many rows were returned? Where did you run the SQL Statement from?
> The total time for a SQL statement includes the time to tech all the
> result set pages. Either there were a lot of rows generated and they
> took time to fetch or the client application fetched rows slowly.
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Perhaps ODBC tracing is turned on at the slow machine?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"subbu" <subbaiahd@.hotmail.com> wrote in message news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> Two (2) rows returned.
> I run sql stmt in query analyser on the machine where sql server installed,
> but connected to server machine using Terminal Services (RDP).
> I have run same query on another machine, which is in the same place like
> previous one did run the query in similar way, it returned results
> differently. Elapsed time is almost matching with CPU. Here also 2 rows
> returned.
> Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> read-ahead reads 0.
> Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads 0,
> read-ahead reads 0.
> Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> read-ahead reads 0.
> Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> read-ahead reads 0.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 167 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 171 ms.
> SQL Server Execution Times:
> CPU time = 156 ms, elapsed time = 173 ms.
> SQL Server parse and compile time:
> CPU time = 0 ms, elapsed time = 0 ms.
> SQL Server Execution Times:
> CPU time = 0 ms, elapsed time = 0 ms.
> Thanks,
> Subbu.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
>> subbu wrote:
>> > Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
>> > read-ahead reads 0.
>> > Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
>> > 0, read-ahead reads 0.
>> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
>> > reads 0, read-ahead reads 0.
>> > Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
>> > 0, read-ahead reads 0.
>> > Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
>> > read-ahead reads 0.
>> >
>> > SQL Server Execution Times:
>> > CPU time = 571 ms, elapsed time = 17560 ms.
>> >
>> > When i run statistics io , and statistics time, on a query (query not
>> > mentioned here), i got above mentioned results.
>> >
>> > From the info given above, I see there are no physical reads, so all
>> > data is there in the memory. I am fuzzled with the cpu time and
>> > elapsed time values. When cpu has taken half a second to process the
>> > data, why elapsed time is
>> > 17.5 sec. I was monitoring cpu when the query being executed, cpu
>> > utilization less than 5% of processor time. So can i derive from this
>> > that remaining 17 sec has been caused to move data between dynmic
>> > memory and cpu memory cache. Or are there some thing i am missing
>> > here. Can you please bring your ideas and let me know if you need any
>> > more info.
>> >
>> > I have run the query in completely quite machine after reboot.
>> >
>> > Thanks,
>> > Subbu.
>> How many rows were returned? Where did you run the SQL Statement from?
>> The total time for a SQL statement includes the time to tech all the
>> result set pages. Either there were a lot of rows generated and they
>> took time to fetch or the client application fetched rows slowly.
>> --
>> David Gugick - SQL Server MVP
>> Quest Software
>|||No. ODBC tracing has not started, i have verified just now.
Thanks,
Subbu.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eO6yBFsTGHA.424@.TK2MSFTNGP12.phx.gbl...
> Perhaps ODBC tracing is turned on at the slow machine?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
news:%23ufnL6rTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> > Two (2) rows returned.
> >
> > I run sql stmt in query analyser on the machine where sql server
installed,
> > but connected to server machine using Terminal Services (RDP).
> >
> > I have run same query on another machine, which is in the same place
like
> > previous one did run the query in similar way, it returned results
> > differently. Elapsed time is almost matching with CPU. Here also 2 rows
> > returned.
> >
> > Table 'vgn_version'. Scan count 8, logical reads 796, physical reads 0,
> > read-ahead reads 0.
> >
> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical reads
0,
> > read-ahead reads 0.
> >
> > Table 'model_grp_v'. Scan count 1, logical reads 255, physical reads 0,
> > read-ahead reads 0.
> >
> > Table 'model_v'. Scan count 1, logical reads 347, physical reads 0,
> > read-ahead reads 0.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 156 ms, elapsed time = 167 ms.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 156 ms, elapsed time = 171 ms.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 156 ms, elapsed time = 173 ms.
> >
> > SQL Server parse and compile time:
> >
> > CPU time = 0 ms, elapsed time = 0 ms.
> >
> > SQL Server Execution Times:
> >
> > CPU time = 0 ms, elapsed time = 0 ms.
> >
> > Thanks,
> >
> > Subbu.
> >
> > "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> > news:u1WodhrTGHA.792@.TK2MSFTNGP10.phx.gbl...
> >> subbu wrote:
> >> > Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0,
> >> > read-ahead reads 0.
> >> > Table 'vgn_version'. Scan count 8, logical reads 875, physical reads
> >> > 0, read-ahead reads 0.
> >> > Table 'vgn_version_tag'. Scan count 2, logical reads 4, physical
> >> > reads 0, read-ahead reads 0.
> >> > Table 'model_grp_v'. Scan count 1, logical reads 258, physical reads
> >> > 0, read-ahead reads 0.
> >> > Table 'model_v'. Scan count 1, logical reads 350, physical reads 0,
> >> > read-ahead reads 0.
> >> >
> >> > SQL Server Execution Times:
> >> > CPU time = 571 ms, elapsed time = 17560 ms.
> >> >
> >> > When i run statistics io , and statistics time, on a query (query
not
> >> > mentioned here), i got above mentioned results.
> >> >
> >> > From the info given above, I see there are no physical reads, so all
> >> > data is there in the memory. I am fuzzled with the cpu time and
> >> > elapsed time values. When cpu has taken half a second to process the
> >> > data, why elapsed time is
> >> > 17.5 sec. I was monitoring cpu when the query being executed, cpu
> >> > utilization less than 5% of processor time. So can i derive from this
> >> > that remaining 17 sec has been caused to move data between dynmic
> >> > memory and cpu memory cache. Or are there some thing i am missing
> >> > here. Can you please bring your ideas and let me know if you need any
> >> > more info.
> >> >
> >> > I have run the query in completely quite machine after reboot.
> >> >
> >> > Thanks,
> >> > Subbu.
> >>
> >> How many rows were returned? Where did you run the SQL Statement from?
> >> The total time for a SQL statement includes the time to tech all the
> >> result set pages. Either there were a lot of rows generated and they
> >> took time to fetch or the client application fetched rows slowly.
> >>
> >> --
> >> David Gugick - SQL Server MVP
> >> Quest Software
> >>
> >
> >

Friday, February 17, 2012

Effect of adding additional CPUs

Wednesday, February 15, 2012

Effect of adding additional CPUs

We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
Enterprise.
What will be the effect on SQL server performance if we add more CPUs ?
Are there any calulations (or numbers available) we can do to determine the
effect of additional CPUs on SQLl server performace ( no of threads, no of
user connections etc)
--
RKIt has a lot to do with what it is you are doing and how you are doing it.
Since that varies so much there is little a std calculation will achieve.
With more CPU's you have the potential to handle more individual
transactions concurrently. You can also potentially do multi-threaded
operations faster such as DBREINDEX and CHECKDB etc. But that does not
guarantee it will. Do you have processor queue issues now?
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> Enterprise.
> What will be the effect on SQL server performance if we add more CPUs ?
> Are there any calulations (or numbers available) we can do to determine
> the
> effect of additional CPUs on SQLl server performace ( no of threads, no of
> user connections etc)
> --
> RK|||no I do not have preocessor queue issues as of now. Ours is a web application
with lot of selects but few insert/updates (hotel reservations). The reason I
am doing this is for future growth. The product has to be able to support
five times the current load. I am guessing increasing the load by 5 time will
definately cause CPU queue issues. But Increasing the CPUs- is it a good
route to take to handle more load ? Any good reading material on this
subject.
--
RK
"Andrew J. Kelly" wrote:
> It has a lot to do with what it is you are doing and how you are doing it.
> Since that varies so much there is little a std calculation will achieve.
> With more CPU's you have the potential to handle more individual
> transactions concurrently. You can also potentially do multi-threaded
> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> guarantee it will. Do you have processor queue issues now?
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> > Enterprise.
> >
> > What will be the effect on SQL server performance if we add more CPUs ?
> > Are there any calulations (or numbers available) we can do to determine
> > the
> > effect of additional CPUs on SQLl server performace ( no of threads, no of
> > user connections etc)
> > --
> > RK
>
>|||Increasing the CPU's is always a good way to deal with increased load but
you need to do a few things first.
Make sure all the code and tables are fully optimized. A bad query or lack
of index can drag down a good server fast.
Make sure you have enough ram to keep the relevant data in cache.
Have a properly configured disk subsystem.
Set the MAXDOP at the server level to less than the total number of procs to
allow concurrent queries in peak times.
--
Andrew J. Kelly SQL MVP
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
> no I do not have preocessor queue issues as of now. Ours is a web
> application
> with lot of selects but few insert/updates (hotel reservations). The
> reason I
> am doing this is for future growth. The product has to be able to support
> five times the current load. I am guessing increasing the load by 5 time
> will
> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> route to take to handle more load ? Any good reading material on this
> subject.
> --
> RK
>
> "Andrew J. Kelly" wrote:
>> It has a lot to do with what it is you are doing and how you are doing
>> it.
>> Since that varies so much there is little a std calculation will achieve.
>> With more CPU's you have the potential to handle more individual
>> transactions concurrently. You can also potentially do multi-threaded
>> operations faster such as DBREINDEX and CHECKDB etc. But that does not
>> guarantee it will. Do you have processor queue issues now?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
>> > Enterprise.
>> >
>> > What will be the effect on SQL server performance if we add more CPUs ?
>> > Are there any calulations (or numbers available) we can do to determine
>> > the
>> > effect of additional CPUs on SQLl server performace ( no of threads, no
>> > of
>> > user connections etc)
>> > --
>> > RK
>>|||What server do you have? We were running 500 users on a Dell 8450 with 4
900Mhz cpus.
It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
3.2ghz hyperthreading cpu's.
The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
perc3/dc controllers.
The compaq has only 6 disks but just wipes the floor with the old dell.
While I've had the dell down I tried replacing the Dell Perc3's with the
latest compaq controllers. Dispite the controllers being U320 they have to
run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
sustained disk throughput increased by a factor of 2 both for reads and
writes. I was never happy with the perc3's but now the compaq controllers
proved the point. Note also that dell array manager does not set up raid 10
correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
have to set up the raid 10 arrays through the dell controller bios to
achieve true raid 10.
Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
channels (dual channel controllers). Write back cache enabled. all stats
unbuffered by windows.
Test tool Sisoft Sandra.
Perc3/DC. Set up by array manager
Sequential Read 33 Mb/sec
Random Read 11 Mb/sec
Sequential Write 22 Mb/sec
Random Write 19 Mb/sec
Perc3/DC. Set up bios
Sequential Read 120 Mb/sec
Random Read 115 Mb/sec
Sequential Write 27 Mb/sec
Random Write 25 Mb/sec
Compaq Smart Array 6402
Sequential Read 228 Mb/sec
Random Read 161 Mb/sec
Sequential Write 47 Mb/sec
Random Write 36 Mb/sec
Shocking.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Increasing the CPU's is always a good way to deal with increased load but
> you need to do a few things first.
> Make sure all the code and tables are fully optimized. A bad query or
> lack of index can drag down a good server fast.
> Make sure you have enough ram to keep the relevant data in cache.
> Have a properly configured disk subsystem.
> Set the MAXDOP at the server level to less than the total number of procs
> to allow concurrent queries in peak times.
> --
> Andrew J. Kelly SQL MVP
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
>> no I do not have preocessor queue issues as of now. Ours is a web
>> application
>> with lot of selects but few insert/updates (hotel reservations). The
>> reason I
>> am doing this is for future growth. The product has to be able to support
>> five times the current load. I am guessing increasing the load by 5 time
>> will
>> definately cause CPU queue issues. But Increasing the CPUs- is it a good
>> route to take to handle more load ? Any good reading material on this
>> subject.
>> --
>> RK
>>
>> "Andrew J. Kelly" wrote:
>> It has a lot to do with what it is you are doing and how you are doing
>> it.
>> Since that varies so much there is little a std calculation will
>> achieve.
>> With more CPU's you have the potential to handle more individual
>> transactions concurrently. You can also potentially do multi-threaded
>> operations faster such as DBREINDEX and CHECKDB etc. But that does not
>> guarantee it will. Do you have processor queue issues now?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
>> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
>> > Enterprise.
>> >
>> > What will be the effect on SQL server performance if we add more CPUs
>> > ?
>> > Are there any calulations (or numbers available) we can do to
>> > determine
>> > the
>> > effect of additional CPUs on SQLl server performace ( no of threads,
>> > no of
>> > user connections etc)
>> > --
>> > RK
>>
>|||Thank you both your comments.
I have SQL Server 2k/ Windows 2003 EE on DELL PE 6850, 8 GB RAM, 4x3.33Ghz
hyperthreaded CPUs two DELL PERC 4/DC controllers. Curreently i am zooming
along. It is only for future I am worried about.
Thanks for the true RAID 10 tip.
--
RK
"Paul Cahill" wrote:
> What server do you have? We were running 500 users on a Dell 8450 with 4
> 900Mhz cpus.
> It failed a month ago and we had to quickly swap in a modern Compaq with 2 x
> 3.2ghz hyperthreading cpu's.
> The dell had 24 disks running raid 10 arrays in two split bus PV200s off two
> perc3/dc controllers.
> The compaq has only 6 disks but just wipes the floor with the old dell.
> While I've had the dell down I tried replacing the Dell Perc3's with the
> latest compaq controllers. Dispite the controllers being U320 they have to
> run U160 as the 15K rpm Fujitsu drives in the dell are a few years old. The
> sustained disk throughput increased by a factor of 2 both for reads and
> writes. I was never happy with the perc3's but now the compaq controllers
> proved the point. Note also that dell array manager does not set up raid 10
> correctly as a stripe of mirrors. It sets it up as a span of mirrors. You
> have to set up the raid 10 arrays through the dell controller bios to
> achieve true raid 10.
> Stats, all on a dell 8450, Windows 2003, Raid 10 on 8 disks over two
> channels (dual channel controllers). Write back cache enabled. all stats
> unbuffered by windows.
> Test tool Sisoft Sandra.
> Perc3/DC. Set up by array manager
> Sequential Read 33 Mb/sec
> Random Read 11 Mb/sec
> Sequential Write 22 Mb/sec
> Random Write 19 Mb/sec
> Perc3/DC. Set up bios
> Sequential Read 120 Mb/sec
> Random Read 115 Mb/sec
> Sequential Write 27 Mb/sec
> Random Write 25 Mb/sec
> Compaq Smart Array 6402
> Sequential Read 228 Mb/sec
> Random Read 161 Mb/sec
> Sequential Write 47 Mb/sec
> Random Write 36 Mb/sec
> Shocking.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23fiU6crFHA.2540@.TK2MSFTNGP09.phx.gbl...
> > Increasing the CPU's is always a good way to deal with increased load but
> > you need to do a few things first.
> >
> > Make sure all the code and tables are fully optimized. A bad query or
> > lack of index can drag down a good server fast.
> > Make sure you have enough ram to keep the relevant data in cache.
> > Have a properly configured disk subsystem.
> > Set the MAXDOP at the server level to less than the total number of procs
> > to allow concurrent queries in peak times.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "RK73" <RK73@.discussions.microsoft.com> wrote in message
> > news:1B595AAC-B34F-4414-8AF4-C6F664171AF0@.microsoft.com...
> >> no I do not have preocessor queue issues as of now. Ours is a web
> >> application
> >> with lot of selects but few insert/updates (hotel reservations). The
> >> reason I
> >> am doing this is for future growth. The product has to be able to support
> >> five times the current load. I am guessing increasing the load by 5 time
> >> will
> >> definately cause CPU queue issues. But Increasing the CPUs- is it a good
> >> route to take to handle more load ? Any good reading material on this
> >> subject.
> >>
> >> --
> >> RK
> >>
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> It has a lot to do with what it is you are doing and how you are doing
> >> it.
> >> Since that varies so much there is little a std calculation will
> >> achieve.
> >> With more CPU's you have the potential to handle more individual
> >> transactions concurrently. You can also potentially do multi-threaded
> >> operations faster such as DBREINDEX and CHECKDB etc. But that does not
> >> guarantee it will. Do you have processor queue issues now?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> >> news:B616692D-BB49-4038-9441-EE9F0756ED67@.microsoft.com...
> >> > We have 4 CPU 8GB Dell server running SQL2k Enterprise on Windows 2003
> >> > Enterprise.
> >> >
> >> > What will be the effect on SQL server performance if we add more CPUs
> >> > ?
> >> > Are there any calulations (or numbers available) we can do to
> >> > determine
> >> > the
> >> > effect of additional CPUs on SQLl server performace ( no of threads,
> >> > no of
> >> > user connections etc)
> >> > --
> >> > RK
> >>
> >>
> >>
> >
> >
>
>

Friday, February 24, 2012

elapsed time vs cpu time

elapsed time vs cpu time

elapsed time vs cpu time

Friday, February 17, 2012

Effect of adding additional CPUs

Effect of adding additional CPUs

Wednesday, February 15, 2012

Effect of adding additional CPUs

Sql Edition Selection

Blog Archive

About Me