Performance impact of Transactional Push Replication

Hi,
I have a transactional push replication from our Publisher on server MM1 and subscriber + distributor on server MM2. We have had performance slowdowns on the MM1 publisher server and we removed the replication to see if this was causing the problem. I b
elieve the only replication process is the sp_replcmd (logreader agent) on the publisher. Are there any statistics that I can use to determine if replication was causing the slowdowns? Any help would be appreciated.
Thanks
Don
Don,
you could use performane monitor and correlate
SQLServer:Replication Dist
SQLServer:Replication Logreader
(Dist:Delivered Cmds/Sec,Dist:Delivered Trans)
with Process:%Processor Time for the sqlserver and sqlserver agent
processes.
Ideally you'd measure the threads used in the replication process and their
contribution, but the above technique should show any large trends.
HTH,
Paul Ibison

Performance impact of tracing?

I have set up a trace using sp_trace_create and it works fine for most of my
SQL 2k SP 3 servers. However on one server (which is no different to the
others) it has a huge impact on performance to the point where i can not run
tracing. The only difference between servers is that this particular server
has a database which uses a huge amount of UDF's?
Anyone had a similar problem?
Since UDF's run for every row processed they can cause a lot of trace events
if you are tracing at the statement level. Try tracing only the Batch
Completed, SP Completed and RPC Completed events instead. You can always
filter down once you find offending statements and open up the trace. Also
make sure you are sending the results to a LOCAL disk drive that is not used
by the data or especially the log file.
Andrew J. Kelly SQL MVP
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:1C0D4DF0-D62D-4258-A658-F44EB85B798F@.microsoft.com...
>I have set up a trace using sp_trace_create and it works fine for most of
>my
> SQL 2k SP 3 servers. However on one server (which is no different to the
> others) it has a huge impact on performance to the point where i can not
> run
> tracing. The only difference between servers is that this particular
> server
> has a database which uses a huge amount of UDF's?
> Anyone had a similar problem?

Performance impact of tracing?

I have set up a trace using sp_trace_create and it works fine for most of my
SQL 2k SP 3 servers. However on one server (which is no different to the
others) it has a huge impact on performance to the point where i can not run
tracing. The only difference between servers is that this particular server
has a database which uses a huge amount of UDF's?
Anyone had a similar problem?Since UDF's run for every row processed they can cause a lot of trace events
if you are tracing at the statement level. Try tracing only the Batch
Completed, SP Completed and RPC Completed events instead. You can always
filter down once you find offending statements and open up the trace. Also
make sure you are sending the results to a LOCAL disk drive that is not used
by the data or especially the log file.
Andrew J. Kelly SQL MVP
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:1C0D4DF0-D62D-4258-A658-F44EB85B798F@.microsoft.com...
>I have set up a trace using sp_trace_create and it works fine for most of
>my
> SQL 2k SP 3 servers. However on one server (which is no different to the
> others) it has a huge impact on performance to the point where i can not
> run
> tracing. The only difference between servers is that this particular
> server
> has a database which uses a huge amount of UDF's?
> Anyone had a similar problem?

Performance impact of tracing?

I have set up a trace using sp_trace_create and it works fine for most of my
SQL 2k SP 3 servers. However on one server (which is no different to the
others) it has a huge impact on performance to the point where i can not run
tracing. The only difference between servers is that this particular server
has a database which uses a huge amount of UDF's?
Anyone had a similar problem?Since UDF's run for every row processed they can cause a lot of trace events
if you are tracing at the statement level. Try tracing only the Batch
Completed, SP Completed and RPC Completed events instead. You can always
filter down once you find offending statements and open up the trace. Also
make sure you are sending the results to a LOCAL disk drive that is not used
by the data or especially the log file.
--
Andrew J. Kelly SQL MVP
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:1C0D4DF0-D62D-4258-A658-F44EB85B798F@.microsoft.com...
>I have set up a trace using sp_trace_create and it works fine for most of
>my
> SQL 2k SP 3 servers. However on one server (which is no different to the
> others) it has a huge impact on performance to the point where i can not
> run
> tracing. The only difference between servers is that this particular
> server
> has a database which uses a huge amount of UDF's?
> Anyone had a similar problem?

Performance impact of Terminal Server on SQL Server

Hello,
We have a Windows 2000 Server box with SQL Server 7.0
installed. The database is maintained by a vendor, but
many customizations (other DBs) are built and maintained
on the same server by us (that's SQLServer={VendorDB1,
VendorDB2,...VendorDBN, OurDB1, OurDB2,...OurDBN}). We
desire to be able to manage the server (of the box, not
SQL Server) remotely via Terminal Server but our vendor
insists against it saying that it will adversely affect
the performance of the database. We do this with other
SQL Server boxes and have not noticed any significant
degradations in performance.
I've noticed the following: if I go to a Win2K Server box
running SQL Server and initiate a terminal server session
*away* from the box (to another machine), the SQL Server
performance drops significantly. However, if I am at a
remote machine and initiate a terminal server session
*to* a box running SQL Server, I experience no
performance problem(s).
Given the above, can anyone point me to any documentation
detailing performance issues related to Terminal Server
and SQL Server on the same machine? Can anyone clarify
the problem for me? That is, is it the case the SQL
Server performance will degrade if Terminal Server is
running *regardless* of whether you are connecting from
the box or to the box?
Thanks!
Quentinone connection into the sql server box for management and
administration should not be a big deal,
i would not run multiple terminal server sessions into a
heavily loaded sql server,
try running perfmon on the terminal server including the
context switches/sec counter.
open one session, run notepad and start typing a normal
rate.
each terminal server session will incur that amount of
load. at some point, the context switching rate will be
high enough to completely disrupt sql server,
but session for administrative purposes only is not bad,
>--Original Message--
>Hello,
>We have a Windows 2000 Server box with SQL Server 7.0
>installed. The database is maintained by a vendor, but
>many customizations (other DBs) are built and maintained
>on the same server by us (that's SQLServer={VendorDB1,
>VendorDB2,...VendorDBN, OurDB1, OurDB2,...OurDBN}). We
>desire to be able to manage the server (of the box, not
>SQL Server) remotely via Terminal Server but our vendor
>insists against it saying that it will adversely affect
>the performance of the database. We do this with other
>SQL Server boxes and have not noticed any significant
>degradations in performance.
>I've noticed the following: if I go to a Win2K Server box
>running SQL Server and initiate a terminal server session
>*away* from the box (to another machine), the SQL Server
>performance drops significantly. However, if I am at a
>remote machine and initiate a terminal server session
>*to* a box running SQL Server, I experience no
>performance problem(s).
>Given the above, can anyone point me to any documentation
>detailing performance issues related to Terminal Server
>and SQL Server on the same machine? Can anyone clarify
>the problem for me? That is, is it the case the SQL
>Server performance will degrade if Terminal Server is
>running *regardless* of whether you are connecting from
>the box or to the box?
>Thanks!
>Quentin
>.
>sql

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,
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
>

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...
>

performance impact of aliasing

hi,

is there a way to reduce the performance overhead of calculations - thin about physical measure "XYZ original" and i create a caculated member called "XYZ" by the following definiton

CREATE MEMBER CURRENTCUBE.[MEASURES].[XYZ]

AS [Measures].[XYZ original],

FORMAT_STRING = "#",

NON_EMPTY_BEHAVIOR = { [XYZ original] },

VISIBLE = 1

I would expect the same performance, but its 15 - 25% slower on my sample querie (

select

{measures.[XYZ]} on columns,

[Dimension].[Hierarchie].members on rows

from

Cube

)

returning 25000 cells.

Does someone know ways to imporve this querie? reduce the overhead of this really simple calculation (alias for the measure name)?

It do this because of .. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1109927&SiteID=1&mode=1

LG, HANNES

Hi Hannes

In your calculated member definition you should replace

NON_EMPTY_BEHAVIOR = { [XYZ original] }

with

NON_EMPTY_BEHAVIOR = [Measures].[XYZ original]

Why? Well - unless you are running SP2 (CTP), you are dealing with a limitation in how the AS engine interprets the NON_EMPTY_BEHAVIOR definitions. For more info, see:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!914.entry

Performance Hit w/ Filegroups?

Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCTTBLKEYFILEGRP
15517322TESTSHIFTX1_TESTSHIFTtest1
15422280TESTSHIFTPK_TESTSHIFTtest1
15422280TESTSHIFTX2_TESTSHIFTtest3
15422280TESTSHIFTX3_TESTSHIFTtest4
15422280TESTSHIFTX4_TESTSHIFTtest1
13319460SHIFTPK_SHIFTtest4
13228153SHIFTX1_SHIFTtest1
13228153SHIFTX2_SHIFTtest1
13228153SHIFTX3_SHIFTtest1
13228153SHIFTX4_SHIFTtest1
13228153SHIFTX5_SHIFTtest2
13228153SHIFTX6_SHIFTtest1
10829933TESTSHFTRCTESTSHFTRCtest3
10748087TESTSHFTRCX1_TESTSHFTRCtest2
10748087TESTSHFTRCX2_TESTSHFTRCtest1
9105520TEST_LOOKUPTEST_LOOKUPtest4
8583208TESTSHIFTSEGPK_TESTSHIFTSEGtest2
8502786TESTSHIFTSEGX1_TESTSHIFTSEGtest1
If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1
|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.
|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.
|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon
sql

Performance Hit w/ Filegroups?

Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon

Performance Hit w/ Filegroups?

Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG te
st2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG te
st1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon