I have two servers - a web server and a data server. I have a web service on
my web server that receives a request for information on anywhere from 1 to
60,000 products. I'm looking at a couple of different approaches for
querying the database for the product information on the 1 to 60,000
products.
1) Pass the product list into a SQL stored procedure as a single delimited
string and have that SQL stored procedure do a SELECT * FROM Products WHERE
Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all of the
details of how I'll get from my single delimited string to that SQL query,
but I assume I can do it.
2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000 calls
to a SQL stored procedure that returns data for a single product. My client
that's issuing the requests is multi-threaded and I'm comfortable that I can
set this up so that I'm issuing the 60,000 calls 5-10 or more calls at a
time.
The client that's requesting the 1 to 60,000 products is not very time
sensitive. It's a batch product feed process that runs 1 to 4 times a day.
The database I'm querying also supports a web site that does have real-time
requirements, so I don't want to structure my query in such a way that my
web service is negatively impacting the performance of my web site.
I'm trying to understand the pros and cons of the two approaches and would
appreciate any inputs. Thoughts I have so far:
Option 1 may perform better for the product feed client because it has one
large network transaction instead of 1 to 60,000 small network transactions.
But Option 1 may put a more intense load on the SQL server for a period of
time, potentially negatively impacting the real-time performance of the web
site.
With either solution I probably want to look at ways to ensure that the
request for data for 1 to 60,000 products is done at a lower priority than
real-time requests from the web site.
Any thoughts or suggestions?
Thanks,
ZoeZoe,
Have a look at this link to see how to pass in a delimited list and process
it appropriately. But I would probably create 2 or more stored procedures to
handle the different ranges of product requests so you can get a proper
query plan for each. For instance if you only had one product specified you
can easily do an index seek and get a good plan. But to retrieve 60K
products you may need to do a scan or Merge Join or even Hash Join. Due to
parameter sniffing if the first time the proc was run it had 50K products
you would get a plan for that many rows. But when you call it the next time
even with 1 product you will still get the same plan as before. So I would
have your app decide how may products there will be and call one of 2 or
more (depends on how many different query plans you may encounter) sps so
they each get their own plan. The case where they only lookup a single
product you can use a straight forward query with an =. More than 1 you need
to use dynamic sql or parse it into a table with a UDF.
http://www.sommarskog.se/arrays-in-sql.html
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>|||"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
Zoe,
Maybe another alternative is to use an XML as a parameter so you can load
easily this XML in a table, add indexes or whatever you need, and do a join
with your product table. I don't recommend option 2 (60000 calls) due to
network overhead and latency. It will be A LOT slower than the one call
alternative.
--
Rubén Garrigós
Solid Quality Mentors|||Zoe Hart (zoe.hart@.nospam.competitive.com) writes:
> 1) Pass the product list into a SQL stored procedure as a single
> delimited string and have that SQL stored procedure do a SELECT * FROM
> Products WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not
> sure of all of the details of how I'll get from my single delimited
> string to that SQL query, but I assume I can do it.
Whatever, don't do exactly this. The time it would take to compile
that statement is amazing, particularly if you are on SQL 2000.
But there are other alternatives, as I discuss in my article
http://www.sommarskog.se/arrays-in-sql.html.
Andrew made an important point about the need for different plans due
to the number of elements in the list. I think the best approach is to
unpack the list into a temp table, as this will cause a recompile
and the actual join is likely to use the best plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks for all the good input. Our rough plan at this point is to have a
stored procedure that accepts an XML input that contains the 1-n SKUs. That
stored procedure will create a temporary table that with columns for the SKU
and the other data we intend to look up. The stored procedure will write the
1-n SKUs to the temporary table and then use UPDATE FROM to join the
temporary table to one or more tables that contain the data we need and
update the columns in the temporary table. We'll then SELECT * from the
temporary table FOR XML to get our results. We can either go with that
result as is or map it to a format we like better in the code that calls the
stored proc.
Thanks again.
Zoe
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>|||Sounds like about the best available approach.
Otherwise, if the query list is really that long, you might upload a
flat file to the server and then import it with an SSIS package.
Note that the numbers, 20k, 60k, are very tiny numbers for data in
terms of modern SQL Server performance, they are just very large
numbers if you try to list them all as part of a SQL command!
Josh
On Tue, 15 Jan 2008 09:52:25 -0500, "Zoe Hart"
<zoe.hart@.nospam.competitive.com> wrote:
>Thanks for all the good input. Our rough plan at this point is to have a
>stored procedure that accepts an XML input that contains the 1-n SKUs. That
>stored procedure will create a temporary table that with columns for the SKU
>and the other data we intend to look up. The stored procedure will write the
>1-n SKUs to the temporary table and then use UPDATE FROM to join the
>temporary table to one or more tables that contain the data we need and
>update the columns in the temporary table. We'll then SELECT * from the
>temporary table FOR XML to get our results. We can either go with that
>result as is or map it to a format we like better in the code that calls the
>stored proc.
>Thanks again.
>Zoe
>"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
>news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>>I have two servers - a web server and a data server. I have a web service
>>on my web server that receives a request for information on anywhere from 1
>>to 60,000 products. I'm looking at a couple of different approaches for
>>querying the database for the product information on the 1 to 60,000
>>products.
>> 1) Pass the product list into a SQL stored procedure as a single delimited
>> string and have that SQL stored procedure do a SELECT * FROM Products
>> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
>> of the details of how I'll get from my single delimited string to that SQL
>> query, but I assume I can do it.
>> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
>> calls to a SQL stored procedure that returns data for a single product. My
>> client that's issuing the requests is multi-threaded and I'm comfortable
>> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
>> calls at a time.
>> The client that's requesting the 1 to 60,000 products is not very time
>> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
>> The database I'm querying also supports a web site that does have
>> real-time requirements, so I don't want to structure my query in such a
>> way that my web service is negatively impacting the performance of my web
>> site.
>> I'm trying to understand the pros and cons of the two approaches and would
>> appreciate any inputs. Thoughts I have so far:
>> Option 1 may perform better for the product feed client because it has one
>> large network transaction instead of 1 to 60,000 small network
>> transactions. But Option 1 may put a more intense load on the SQL server
>> for a period of time, potentially negatively impacting the real-time
>> performance of the web site.
>> With either solution I probably want to look at ways to ensure that the
>> request for data for 1 to 60,000 products is done at a lower priority than
>> real-time requests from the web site.
>> Any thoughts or suggestions?
>> Thanks,
>> Zoe
>
Showing posts with label web. Show all posts
Showing posts with label web. Show all posts
Friday, March 30, 2012
Performance impact of different approaches to querying for 20,000 rows of data
I have two servers - a web server and a data server. I have a web service on
my web server that receives a request for information on anywhere from 1 to
60,000 products. I'm looking at a couple of different approaches for
querying the database for the product information on the 1 to 60,000
products.
1) Pass the product list into a SQL stored procedure as a single delimited
string and have that SQL stored procedure do a SELECT * FROM Products WHERE
Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all of the
details of how I'll get from my single delimited string to that SQL query,
but I assume I can do it.
2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000 calls
to a SQL stored procedure that returns data for a single product. My client
that's issuing the requests is multi-threaded and I'm comfortable that I can
set this up so that I'm issuing the 60,000 calls 5-10 or more calls at a
time.
The client that's requesting the 1 to 60,000 products is not very time
sensitive. It's a batch product feed process that runs 1 to 4 times a day.
The database I'm querying also supports a web site that does have real-time
requirements, so I don't want to structure my query in such a way that my
web service is negatively impacting the performance of my web site.
I'm trying to understand the pros and cons of the two approaches and would
appreciate any inputs. Thoughts I have so far:
Option 1 may perform better for the product feed client because it has one
large network transaction instead of 1 to 60,000 small network transactions.
But Option 1 may put a more intense load on the SQL server for a period of
time, potentially negatively impacting the real-time performance of the web
site.
With either solution I probably want to look at ways to ensure that the
request for data for 1 to 60,000 products is done at a lower priority than
real-time requests from the web site.
Any thoughts or suggestions?
Thanks,
Zoe
Zoe,
Have a look at this link to see how to pass in a delimited list and process
it appropriately. But I would probably create 2 or more stored procedures to
handle the different ranges of product requests so you can get a proper
query plan for each. For instance if you only had one product specified you
can easily do an index seek and get a good plan. But to retrieve 60K
products you may need to do a scan or Merge Join or even Hash Join. Due to
parameter sniffing if the first time the proc was run it had 50K products
you would get a plan for that many rows. But when you call it the next time
even with 1 product you will still get the same plan as before. So I would
have your app decide how may products there will be and call one of 2 or
more (depends on how many different query plans you may encounter) sps so
they each get their own plan. The case where they only lookup a single
product you can use a straight forward query with an =. More than 1 you need
to use dynamic sql or parse it into a table with a UDF.
http://www.sommarskog.se/arrays-in-sql.html
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Zoe Hart (zoe.hart@.nospam.competitive.com) writes:
> 1) Pass the product list into a SQL stored procedure as a single
> delimited string and have that SQL stored procedure do a SELECT * FROM
> Products WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not
> sure of all of the details of how I'll get from my single delimited
> string to that SQL query, but I assume I can do it.
Whatever, don't do exactly this. The time it would take to compile
that statement is amazing, particularly if you are on SQL 2000.
But there are other alternatives, as I discuss in my article
http://www.sommarskog.se/arrays-in-sql.html.
Andrew made an important point about the need for different plans due
to the number of elements in the list. I think the best approach is to
unpack the list into a temp table, as this will cause a recompile
and the actual join is likely to use the best plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for all the good input. Our rough plan at this point is to have a
stored procedure that accepts an XML input that contains the 1-n SKUs. That
stored procedure will create a temporary table that with columns for the SKU
and the other data we intend to look up. The stored procedure will write the
1-n SKUs to the temporary table and then use UPDATE FROM to join the
temporary table to one or more tables that contain the data we need and
update the columns in the temporary table. We'll then SELECT * from the
temporary table FOR XML to get our results. We can either go with that
result as is or map it to a format we like better in the code that calls the
stored proc.
Thanks again.
Zoe
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Sounds like about the best available approach.
Otherwise, if the query list is really that long, you might upload a
flat file to the server and then import it with an SSIS package.
Note that the numbers, 20k, 60k, are very tiny numbers for data in
terms of modern SQL Server performance, they are just very large
numbers if you try to list them all as part of a SQL command!
Josh
On Tue, 15 Jan 2008 09:52:25 -0500, "Zoe Hart"
<zoe.hart@.nospam.competitive.com> wrote:
>Thanks for all the good input. Our rough plan at this point is to have a
>stored procedure that accepts an XML input that contains the 1-n SKUs. That
>stored procedure will create a temporary table that with columns for the SKU
>and the other data we intend to look up. The stored procedure will write the
>1-n SKUs to the temporary table and then use UPDATE FROM to join the
>temporary table to one or more tables that contain the data we need and
>update the columns in the temporary table. We'll then SELECT * from the
>temporary table FOR XML to get our results. We can either go with that
>result as is or map it to a format we like better in the code that calls the
>stored proc.
>Thanks again.
>Zoe
>"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
>news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>
my web server that receives a request for information on anywhere from 1 to
60,000 products. I'm looking at a couple of different approaches for
querying the database for the product information on the 1 to 60,000
products.
1) Pass the product list into a SQL stored procedure as a single delimited
string and have that SQL stored procedure do a SELECT * FROM Products WHERE
Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all of the
details of how I'll get from my single delimited string to that SQL query,
but I assume I can do it.
2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000 calls
to a SQL stored procedure that returns data for a single product. My client
that's issuing the requests is multi-threaded and I'm comfortable that I can
set this up so that I'm issuing the 60,000 calls 5-10 or more calls at a
time.
The client that's requesting the 1 to 60,000 products is not very time
sensitive. It's a batch product feed process that runs 1 to 4 times a day.
The database I'm querying also supports a web site that does have real-time
requirements, so I don't want to structure my query in such a way that my
web service is negatively impacting the performance of my web site.
I'm trying to understand the pros and cons of the two approaches and would
appreciate any inputs. Thoughts I have so far:
Option 1 may perform better for the product feed client because it has one
large network transaction instead of 1 to 60,000 small network transactions.
But Option 1 may put a more intense load on the SQL server for a period of
time, potentially negatively impacting the real-time performance of the web
site.
With either solution I probably want to look at ways to ensure that the
request for data for 1 to 60,000 products is done at a lower priority than
real-time requests from the web site.
Any thoughts or suggestions?
Thanks,
Zoe
Zoe,
Have a look at this link to see how to pass in a delimited list and process
it appropriately. But I would probably create 2 or more stored procedures to
handle the different ranges of product requests so you can get a proper
query plan for each. For instance if you only had one product specified you
can easily do an index seek and get a good plan. But to retrieve 60K
products you may need to do a scan or Merge Join or even Hash Join. Due to
parameter sniffing if the first time the proc was run it had 50K products
you would get a plan for that many rows. But when you call it the next time
even with 1 product you will still get the same plan as before. So I would
have your app decide how may products there will be and call one of 2 or
more (depends on how many different query plans you may encounter) sps so
they each get their own plan. The case where they only lookup a single
product you can use a straight forward query with an =. More than 1 you need
to use dynamic sql or parse it into a table with a UDF.
http://www.sommarskog.se/arrays-in-sql.html
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Zoe Hart (zoe.hart@.nospam.competitive.com) writes:
> 1) Pass the product list into a SQL stored procedure as a single
> delimited string and have that SQL stored procedure do a SELECT * FROM
> Products WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not
> sure of all of the details of how I'll get from my single delimited
> string to that SQL query, but I assume I can do it.
Whatever, don't do exactly this. The time it would take to compile
that statement is amazing, particularly if you are on SQL 2000.
But there are other alternatives, as I discuss in my article
http://www.sommarskog.se/arrays-in-sql.html.
Andrew made an important point about the need for different plans due
to the number of elements in the list. I think the best approach is to
unpack the list into a temp table, as this will cause a recompile
and the actual join is likely to use the best plan.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for all the good input. Our rough plan at this point is to have a
stored procedure that accepts an XML input that contains the 1-n SKUs. That
stored procedure will create a temporary table that with columns for the SKU
and the other data we intend to look up. The stored procedure will write the
1-n SKUs to the temporary table and then use UPDATE FROM to join the
temporary table to one or more tables that contain the data we need and
update the columns in the temporary table. We'll then SELECT * from the
temporary table FOR XML to get our results. We can either go with that
result as is or map it to a format we like better in the code that calls the
stored proc.
Thanks again.
Zoe
"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>I have two servers - a web server and a data server. I have a web service
>on my web server that receives a request for information on anywhere from 1
>to 60,000 products. I'm looking at a couple of different approaches for
>querying the database for the product information on the 1 to 60,000
>products.
> 1) Pass the product list into a SQL stored procedure as a single delimited
> string and have that SQL stored procedure do a SELECT * FROM Products
> WHERE Sku IN ('sku1', 'sku2', 'sku3',..., 'sku60000'). I'm not sure of all
> of the details of how I'll get from my single delimited string to that SQL
> query, but I assume I can do it.
> 2) Loop through the list of 1 to 60,000 products and issue 1 to 60,000
> calls to a SQL stored procedure that returns data for a single product. My
> client that's issuing the requests is multi-threaded and I'm comfortable
> that I can set this up so that I'm issuing the 60,000 calls 5-10 or more
> calls at a time.
> The client that's requesting the 1 to 60,000 products is not very time
> sensitive. It's a batch product feed process that runs 1 to 4 times a day.
> The database I'm querying also supports a web site that does have
> real-time requirements, so I don't want to structure my query in such a
> way that my web service is negatively impacting the performance of my web
> site.
> I'm trying to understand the pros and cons of the two approaches and would
> appreciate any inputs. Thoughts I have so far:
> Option 1 may perform better for the product feed client because it has one
> large network transaction instead of 1 to 60,000 small network
> transactions. But Option 1 may put a more intense load on the SQL server
> for a period of time, potentially negatively impacting the real-time
> performance of the web site.
> With either solution I probably want to look at ways to ensure that the
> request for data for 1 to 60,000 products is done at a lower priority than
> real-time requests from the web site.
> Any thoughts or suggestions?
> Thanks,
> Zoe
>
|||Sounds like about the best available approach.
Otherwise, if the query list is really that long, you might upload a
flat file to the server and then import it with an SSIS package.
Note that the numbers, 20k, 60k, are very tiny numbers for data in
terms of modern SQL Server performance, they are just very large
numbers if you try to list them all as part of a SQL command!
Josh
On Tue, 15 Jan 2008 09:52:25 -0500, "Zoe Hart"
<zoe.hart@.nospam.competitive.com> wrote:
>Thanks for all the good input. Our rough plan at this point is to have a
>stored procedure that accepts an XML input that contains the 1-n SKUs. That
>stored procedure will create a temporary table that with columns for the SKU
>and the other data we intend to look up. The stored procedure will write the
>1-n SKUs to the temporary table and then use UPDATE FROM to join the
>temporary table to one or more tables that contain the data we need and
>update the columns in the temporary table. We'll then SELECT * from the
>temporary table FOR XML to get our results. We can either go with that
>result as is or map it to a format we like better in the code that calls the
>stored proc.
>Thanks again.
>Zoe
>"Zoe Hart" <zoe.hart@.nospam.competitive.com> wrote in message
>news:eNn1t%235UIHA.1480@.TK2MSFTNGP06.phx.gbl...
>
Friday, March 23, 2012
performance counters
well, it doesn't look so good.
i did all the troubleshoot i could find in the web but with a minor success.
it's not a corrupted registry, counters or something like that. out of 5
different machine - only one agreed to cooperate and allow access to those
counters remotely. the rest had varius behavior...
i tried lodctr, unlodctr, exctrlst, diskperf -Y and re-register sqlctr80.dll
with regsvr32 - with no maijor success. even though i got an error message
for the command "regsvr32 sqlctr80.dll" about an entry point that it couldnt
find, i don't think that's the problem.
any more assistance will be appreciated.
thanks, em.
"Plamen Ratchev" wrote:
> The following steps may help (but not always):
> - At the command prompt run: unlodctr.exe MSSQLServer
> - Then run: lodctr.exe <SQL Server path>\binn\sqlctr.ini
> - Reboot the computer
> If you have a named instance you should use the named instance name (for
> example: unlodctr.exe MSSQL$InstanceName).
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>
> "em" <em@.discussions.microsoft.com> wrote in message
> news:C2C1B0E8-2FE0-488F-A085-C392CDA89E5C@.microsoft.com...
>
>
Can you please clarify if you can access the performance counters on the
local machine and the problem is with remote access? How is that one machine
that works fine different than the others? Are there any related messages in
Event Log or the log files?
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"em" <em@.discussions.microsoft.com> wrote in message
news:8DA8FEEC-77F4-451C-9508-280F5F038745@.microsoft.com...[vbcol=seagreen]
> well, it doesn't look so good.
> i did all the troubleshoot i could find in the web but with a minor
> success.
> it's not a corrupted registry, counters or something like that. out of 5
> different machine - only one agreed to cooperate and allow access to those
> counters remotely. the rest had varius behavior...
> i tried lodctr, unlodctr, exctrlst, diskperf -Y and re-register
> sqlctr80.dll
> with regsvr32 - with no maijor success. even though i got an error message
> for the command "regsvr32 sqlctr80.dll" about an entry point that it
> couldnt
> find, i don't think that's the problem.
> any more assistance will be appreciated.
> thanks, em.
> "Plamen Ratchev" wrote:
i did all the troubleshoot i could find in the web but with a minor success.
it's not a corrupted registry, counters or something like that. out of 5
different machine - only one agreed to cooperate and allow access to those
counters remotely. the rest had varius behavior...
i tried lodctr, unlodctr, exctrlst, diskperf -Y and re-register sqlctr80.dll
with regsvr32 - with no maijor success. even though i got an error message
for the command "regsvr32 sqlctr80.dll" about an entry point that it couldnt
find, i don't think that's the problem.
any more assistance will be appreciated.
thanks, em.
"Plamen Ratchev" wrote:
> The following steps may help (but not always):
> - At the command prompt run: unlodctr.exe MSSQLServer
> - Then run: lodctr.exe <SQL Server path>\binn\sqlctr.ini
> - Reboot the computer
> If you have a named instance you should use the named instance name (for
> example: unlodctr.exe MSSQL$InstanceName).
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>
> "em" <em@.discussions.microsoft.com> wrote in message
> news:C2C1B0E8-2FE0-488F-A085-C392CDA89E5C@.microsoft.com...
>
>
Can you please clarify if you can access the performance counters on the
local machine and the problem is with remote access? How is that one machine
that works fine different than the others? Are there any related messages in
Event Log or the log files?
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"em" <em@.discussions.microsoft.com> wrote in message
news:8DA8FEEC-77F4-451C-9508-280F5F038745@.microsoft.com...[vbcol=seagreen]
> well, it doesn't look so good.
> i did all the troubleshoot i could find in the web but with a minor
> success.
> it's not a corrupted registry, counters or something like that. out of 5
> different machine - only one agreed to cooperate and allow access to those
> counters remotely. the rest had varius behavior...
> i tried lodctr, unlodctr, exctrlst, diskperf -Y and re-register
> sqlctr80.dll
> with regsvr32 - with no maijor success. even though i got an error message
> for the command "regsvr32 sqlctr80.dll" about an entry point that it
> couldnt
> find, i don't think that's the problem.
> any more assistance will be appreciated.
> thanks, em.
> "Plamen Ratchev" wrote:
Wednesday, March 21, 2012
Performance cost of joins
We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.
Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:
-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC
its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...
for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....
for reports u may use views to avoid repeated use of joins...
so optimal for me = normalized + views
Performance cost of joins
We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.
Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:
-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC
its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...
for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....
for reports u may use views to avoid repeated use of joins...
so optimal for me = normalized + views
Performance cost of joins
We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||
It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.
Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:
-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC
its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...
for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....
for reports u may use views to avoid repeated use of joins...
so optimal for me = normalized + views
Performance clues
I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
Performance clues
I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||cbrichards via SQLMonster.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||cbrichards via SQLMonster.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
Performance clues
I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficul
t.
I happened to have profiler running during one of these timeout episodes. Th
e
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on th
e
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (abou
t
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...[vbcol
=seagreen]
>I have a web application that calls stored procedures that sometimes timeou
t
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>[/vbcol]|||cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeo
ut
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficul
t.
I happened to have profiler running during one of these timeout episodes. Th
e
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on th
e
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (abou
t
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...[vbcol
=seagreen]
>I have a web application that calls stored procedures that sometimes timeou
t
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>[/vbcol]|||cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeo
ut
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>
performance bottleneck?
how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi
"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegr oups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi
"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegr oups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi
performance bottleneck?
how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi
performance bottleneck?
how many users can a box with 8 processors and 32 GB RAM with sql
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi
server 2000 on windows server 2003 handle effectively?
my web server with iis 6.0 on windows server 2003 has 4 processors and
8 GB RAM.
i have approx. 5000 active users - could give the connections in a few
days.
should i add more RAM/processing power on the web server to improve
performance?
or is it the bandwidth?
thanks
mdgandhi"mdGandhi" <gandhimanisha@.gmail.com> wrote in message
news:1172497798.361738.245830@.z35g2000cwz.googlegroups.com...
> how many users can a box with 8 processors and 32 GB RAM with sql
> server 2000 on windows server 2003 handle effectively?
> my web server with iis 6.0 on windows server 2003 has 4 processors and
> 8 GB RAM.
> i have approx. 5000 active users - could give the connections in a few
> days.
> should i add more RAM/processing power on the web server to improve
> performance?
> or is it the bandwidth?
> thanks
> mdgandhi
>
1 user
Or 50,0000.
Honestly, there's not enough information here to really give an answer.
For example, I had a 6 year old box (quad Xeon 700Mhz CPU) handling millions
of transactions an hour and probably could have handled another 30% in
traffic. So your box would be way overkill for that.
On the other hand I had a 4 year old box (dual Xeon CPU) handling a few
thousand transactions an hour that was overwhelmeed as it was. The
difference, the nature of the transactions.
That said, what you describe is a fairly beefy box. But it really depends
on what the users are doing.
Generally, focus on getting your queries to be effecient and to return no
more information than necessary. Then you can focus on bottlenecks after
taht.
I note for example you don't mention the disk subsystem at all. This can
have a HUGE impact on performance, especially if you're at all disk I/O
intensive.
Google around, there's a few books out there for calculating SQL Server
capacities. They may help you.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||The short answer is that there is no simple rule for determining how
many users a configuration can handle because it depends on the
specific work being performed. The best approach is to analyze the
current bottlenecks by analyzing how the machine runs now. Performance
Monitor is your friend.
Roy Harvey
Beacon Falls, CT
On 26 Feb 2007 05:49:58 -0800, "mdGandhi" <gandhimanisha@.gmail.com>
wrote:
>how many users can a box with 8 processors and 32 GB RAM with sql
>server 2000 on windows server 2003 handle effectively?
>my web server with iis 6.0 on windows server 2003 has 4 processors and
>8 GB RAM.
>i have approx. 5000 active users - could give the connections in a few
>days.
>should i add more RAM/processing power on the web server to improve
>performance?
>or is it the bandwidth?
>thanks
>mdgandhi
Tuesday, March 20, 2012
Performance Anomaly
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?
When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?
|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?
When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?
|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>
Performance Anomaly
I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?Run a Profiler trace and see exactly how the procedure is being called
from your web application.|||Please do not multipost. I answered in .server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <TomMetrinex@.discussions.microsoft.com> wrote in message
news:5286E608-C003-4F01-B2AF-7913134C2996@.microsoft.com...
I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?Run a Profiler trace and see exactly how the procedure is being called
from your web application.|||Please do not multipost. I answered in .server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <TomMetrinex@.discussions.microsoft.com> wrote in message
news:5286E608-C003-4F01-B2AF-7913134C2996@.microsoft.com...
I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?
Performance Anomaly
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 1
9
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there'
s
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could
be
> that these options are different between Query Analyzer and your applicati
on
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in messa
ge
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 1
9
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there'
s
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could
be
> that these options are different between Query Analyzer and your applicati
on
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in messa
ge
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>
Performance Anomaly
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in 19
seconds. What can cause this severe difference in the performance of this
stored procedure?When you are running the procedure from your application, make sure there's
no blocking happening in the server. You could use sp_who to check this.
Also, some of the SET options play a role in the query plan, and it could be
that these options are different between Query Analyzer and your application
connection.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
I am currently having a strange performance problem with one of my stored
procedures. If I run the stored procedure from my web application or from
Visual Studio it will timeout after 5 minutes (my timeout setting). If run
the stored procedure using SQL Query Analyzer it executes without issue in
19
seconds. What can cause this severe difference in the performance of this
stored procedure?|||I have seen no blocking or a difference in the connection set options. Are
there any other possiblities? In the event I see blocking what should I do?
What does this indicate, is the server overloaded?
"Narayana Vyas Kondreddi" wrote:
> When you are running the procedure from your application, make sure there's
> no blocking happening in the server. You could use sp_who to check this.
> Also, some of the SET options play a role in the query plan, and it could be
> that these options are different between Query Analyzer and your application
> connection.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Tom @. Metrinex" <Tom @. Metrinex@.discussions.microsoft.com> wrote in message
> news:C90F4AF2-6C23-4465-961A-434925E83CBE@.microsoft.com...
> I am currently having a strange performance problem with one of my stored
> procedures. If I run the stored procedure from my web application or from
> Visual Studio it will timeout after 5 minutes (my timeout setting). If run
> the stored procedure using SQL Query Analyzer it executes without issue in
> 19
> seconds. What can cause this severe difference in the performance of this
> stored procedure?
>
>
Monday, March 12, 2012
Performance & Threading
Hi,
I am a web developer using c# and we use threading extensively across our website. I noticed that we appeared to be geting major speed degradation when seperate threads would make calls to the same SPC (with different params). After creating a few diferent test scenarios I ended up using a console application that allows me to spawn n threads to a SQL Server and then tells me the results.
We first saw this issue on SQL Server 2000 but we recently upgraded our stage server to SQL Server 2005 so I ran the tests there,
The Stage server is a 4CPU dual core opteron box, so there are 8 cores. There was very little activity on the server, no more than 1 or 2% cpu utilization.
The call was executed using: 'EXEC DBName.dbo.TestSPC' and dbo was the owner of the SPC. The SPC contained a simple select on the primary key, the table has about 19000 rows:
SELECT * FROM People WHERE id between 10000 and 20000
When the SPC is called serially, it takes (in ms):
188, 203, 188, 234, 219, 250, 172, 203 - total time including connections = 1766ms
If I send all 8 threads at the same time, each with thier own connection the results are:
922, 906, 922, 1016, 1000, 1203, 1172, 1047 - total time including connections = 1313ms
My questions are:
- Is this expected behaviour on an 8 core 2005 server? Why would the first thread take nearly 5 times longer to complete? I could understand some extra overhead, but 500% seems excessive. Running serially, is it usual for an SPC to vary in execution time as seen above. I ask this because if I execute a diferent SPC which is much more complex multiple times in a row in Query Analyzer, then I see variations from 800ms up to 5000ms on our stage server.
Thanks for looking.
Jim
What are the specs on the client box you are submitting the requests from? If you are spawning 8 threads on a single core box, obviously each thread's work is going to be serialized to some degree on the client box, so that may be part of it.
Also, are the times listed based on times reported from your client application or the server? To see how the server is actually handling each request, I'd recommend running a server-side SQL trace to capture the duration, reads, writes, and resource usage on the server, not on the client...you should notice that regardless of the times reported on the client, the times to execute the procedures on the server are relatively static after the initial compilation, optimization, etc. of the procedure. Times reported on the client could include network latencies, client latencies, etc.
Also, in your scenario above, when using multiple threads you mention that each thread uses it's own connection...do the times include creation of the connection in addition to execution and response to the query itself? If so, in the single-threaded attempt are you doing the same (i.e. creating/destroying connection on each execution attempt)?
Finally, an obvious possible issue in the multi-threaded scenario is blocking on the server...each simultaneous request to access the same records on the server will be blocked until the previous request(s) have been processed...you'd want to monitor the server to see if spids are getting blocked by others during the execution...note that blocked time is included in trace duration data, so that could be misleading if it's an issue...
|||What are the specs on the client box you are submitting the requests from? If you are spawning 8 threads on a single core box, obviously each thread's work is going to be serialized to some degree on the client box, so that may be part of it.
Those times were done using terminal server directly on the SQL box. It was quicker from our Stage IIS server which is a 2 cpu HT machine (3.0ghz I believe).
Also, are the times listed based on times reported from your client application or the server? To see how the server is actually handling each request, I'd recommend running a server-side SQL trace to capture the duration, reads, writes, and resource usage on the server, not on the client...you should notice that regardless of the times reported on the client, the times to execute the procedures on the server are relatively static after the initial compilation, optimization, etc. of the procedure. Times reported on the client could include network latencies, client latencies, etc.
I did this when I was testing from other boxes and the time diferences were negligable. However this was on the box itself, so there were no other factors such as client speed, or network latency that I can think of.
Also, in your scenario above, when using multiple threads you mention that each thread uses it's own connection...do the times include creation of the connection in addition to execution and response to the query itself? If so, in the single-threaded attempt are you doing the same (i.e. creating/destroying connection on each execution attempt)?
Connection creation times have already been removed. In single threaded mode, and in multi-threaded mode, each operation has it's own connection, although in single threaded mode, it will reuse the same connection from the conneciton pool. However these are purely times using a SQLAdapter on an open connection, as I also have a time reading on how long each connection takes to open. The code for the fill is.
DateTime fillStart = DateTime.Now;
SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adp.Fill(ds);
DateTime fillEnd = DateTime.Now;
Finally, an obvious possible issue in the multi-threaded scenario is blocking on the server...each simultaneous request to access the same records on the server will be blocked until the previous request(s) have been processed...you'd want to monitor the server to see if spids are getting blocked by others during the execution...note that blocked time is included in trace duration data, so that could be misleading if it's an issue...
What you mentioned here is what I thought would happen, the first thread would return in the same time and then subsequent threads would return faster than in single threaded mode, but not in the same time as 1 thread + connection + thread creation time. I dont understand why the first thread the box receives is held up by 400% of its execution time.
I will run another trace on the server whilst running the console directly from the server, I will add the options you mentioned above: duration, reads, writes, and resource usage on the server, plus I will add locks. I will also run perfmon at the same time to see what is going on on the box.
Thanks for your help, I'll post more results in a few hours.
|||You've also got to look at caching, IO capabilities, memory and network. You are selecting 10,000 records that is a huge number, and will undoubtedly lead to a level of blocking. What transaction isolation level is being used to access the data, this can change the amount of blocking that will occur.
In a good design you should not see that level of disparity, which suggests you are hitting some limit, which I would suggest is due to the amount of data.
Friday, March 9, 2012
Performance
Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....
You want to minimize the amount of data that has to travel over the
network connection between the servers. I'd put all the SQL stuff
(tables and stored procedures) on one server, all the web front-end
stuff (HTML/ASP/PHP/whatever and images) on the other.
Memory is faster than disk is faster than network, so:
Find the bottleneck.
Have stored procedures on one server and data on another? Really bad
idea from all points of view. More network traffic, and more risk
for things not working at all.
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....
thanks
MarkMark wrote:
Quote:
Originally Posted by
Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
You want to minimize the amount of data that has to travel over the
network connection between the servers. I'd put all the SQL stuff
(tables and stored procedures) on one server, all the web front-end
stuff (HTML/ASP/PHP/whatever and images) on the other.
Quote:
Originally Posted by
looking for some ideas...on how to make this server performance
increase....
Memory is faster than disk is faster than network, so:
1) Maximize RAM. There's some slightly non-trivial configuration
involved in getting SQL Server to use more than 2 GB. But don't
let it use quite /all/ the RAM, you need to leave some for the OS.
2) Optimize disk usage. Ideally, keep data, temp, logs, and other
stuff (including the OS) separate. Someone more familiar with
the different types of RAID can chime in here.
3) Minimize network traffic. Filter data server-side, unless the
ugliness of the resulting code outweighs the speed gain.|||"Mark" <analizer1@.yahoo.comwrote in message
news:TI_Ah.14917$O02.4071@.newssvr11.news.prodigy.n et...
Quote:
Originally Posted by
Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....
Find the bottleneck.
There's some good books and articles out there on performance tuning. But
figure out if you have lots of disk I/O, CPU or what.
Are your tables properly indexed for example? If not, that's a good place
to look for example.
Quote:
Originally Posted by
>
thanks
Mark
>
Quote:
Originally Posted by
Hi...I have a server that responds to web pages
and back end processing...im not sure the best place to start to increase
performance....
im a programmer..not a super dba but im pretty good...
i have two servers at the isp site...was
thinking of putting all the backen store procedures on one server and when
there invoked to retrieve the record sets from server1 ...
looking for some ideas...on how to make this server performance
increase....
Have stored procedures on one server and data on another? Really bad
idea from all points of view. More network traffic, and more risk
for things not working at all.
But if you have web server and SQL Server on the same box, it would be a
good idea to separate them.
If you have multiple databases for multiple clients, it could also be
an idea to scale out. Use Profiler to see which databses that get the
most traffic.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)