Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts

Friday, March 30, 2012

Performance Tool Question

I am looking for a tool that will help optimize TSQL and Stored Procedures
in SQL Server 2000. Can anybody recommend a good tool?
I need something that will scan a TSQL, Stored Procedure or View and
recommend optimizations.
Thanks.Take a look at www.superiorsql.com
This is a tool to build sql statements on the fly, don't know whether this
tool has a performance-optimizer
Within query analyzer you can show the execution plan for each stored
procedure.
Open a new window in query analyzer and type execute myprocedurename ( type
an existing procedure name in the field myprocedurename)
The execution plan will show all scans to be performed and will give hints
to improve (for instance building extra indexes).
Performance-optimizing is the essence of a DBMS so I guess you have to use
the internal algorithms by using the execution plan of MSSQL
"Ed Willis" <ed_willis@.acsi.orgnospam> schreef in bericht
news:%23X682%234wEHA.2732@.TK2MSFTNGP12.phx.gbl...
> I am looking for a tool that will help optimize TSQL and Stored Procedures
> in SQL Server 2000. Can anybody recommend a good tool?
> I need something that will scan a TSQL, Stored Procedure or View and
> recommend optimizations.
> Thanks.
>|||Hi Ed,
Quest Software has a ton of tuning and optimization tools built into their
Quest Central for SQL Server product. You can get an older version as
freeware at www.quest.com/quest_central/sql_server/freeware/. Check out the
Database Analysis tool in the freeware product. The latest version has a
lot more SQL tuning tools where it will look at your SQL statements and
recommend much better performing queries. I'm not sure if that version is
in trialware or freeware yet.
Hope this helps,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support the PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:%23X682%234wEHA.2732@.TK2MSFTNGP12.phx.gbl...
> I am looking for a tool that will help optimize TSQL and Stored Procedures
> in SQL Server 2000. Can anybody recommend a good tool?
> I need something that will scan a TSQL, Stored Procedure or View and
> recommend optimizations.
> Thanks.
>

Monday, March 26, 2012

Performance questions

We have a production database that sits on a 4 proc server with 4 GB of memory and SAN disk storage via fiber. There are some stored procedures that run and they take approximately 10 minutes to run. A developer has SQL Server installed on his local pc that has 1 2.5 GHz processor and 2 GB of memory and the stored procedures run in approximately 2 minutes. I have updated statistics and rebuilt indexes to no avail. He is questioning why it runs so much faster on his smaller pc compared to the production environment. I have monitored CPU, Memory, and Disk Queue Length and none of these performance counters look concerning to me while the stored procedures are running.

Can anyone out there give me some input on what I could check to figure out why we are experiencing this performance difference?

Thanks,

Corey

Do both databases have the same volume of data? You did not mention what speed and type of processors were in the server. If it is an older server, with say, 1GHz Pentium 3 based Xeon CPU's, you could easily see much slower query performance than on a developer's workstation, with a single faster CPU.

You should also look at the graphical execution plan for the query on the server, and see whether or not the plan is being parallelized or not. BTW, what version of SQL Server are you running?

|||

The data is the same between the two environments. His pc has a 2.5 GHz Intel processor and our server has 4 2.7 GHz Intel processors. We are running SQL Server 2000 SP3a. I will try to look at the queries but their are hundreds. Should the query plan be the same since I ran update statistics, rebuild indexes, and then the process so that I was comparing as each as close as possible.

|||

Just pick two or three commonly executed queries and run them in Query Analyzer with the graphical execution plan turned on and with SET STATISTICS IO on (just run that statement before you run the queries).

Do you see similar query plans and I/O statistics between the developer's workstation and your server?

Are we comparing a developer's workstation with no load with a production server with a full-work load?

If so, I would start looking at the production server to see if you see signs of CPU pressure, memory pressure, I/O bottlenecks, etc.

|||

I will try to get a couple of queries and compare them as suggested.

The developer's workstation has no load and the production server does have other work going on but this server does have plenty of resources left and it is a much larger box than the developer's workstation. I will go ahead and grab some perfmon counters to verify this too.

sql

Performance Question: Cursor-based vs Set-based

I was researching opinions on using cursors in stored procedures and found a thread http://www.msdner.com/forum/thread143091.html discussing (generally) why not to use them.

I took a cursor-based solution and took a stab at writing the equivalent to it as a set-based procedure. I then timed each process using Query Profiler by executing each 20 consecutive times in Query Analyzer (i.e. EXECUTE sp_CalculateRewards and EXECUTE sp_CalculateRewards2) With respect to data size and row counts, of the approximately 12,000 rows in the customer table, only 85 records meet the criteria for processing.

Perhaps my set-based solution is way off, but the cursor based solution consistently ran in the 500ms range (avg 525ms), while my set-based one ran (progressively better) in cycles of 4000ms, 2000ms, 600ms, 4000ms...etc... repeating this pattern over the 20 repeated executions.

My two questions are this. First, does my set-based approach seem reasonably correct or is there a better way. Second, has anyone seen the same anomaly with respect to the timings and is there a reason for it?

Thanks for your input!

The cursor based solution: delete all records from reporting table...open cursor for those customers from the customer table with an enrollment date...calculate sales for prior 365 days from the enrollment date and then year to date sales since enrollment date...build an INSERT statement and EXECUTE the statement thus inserting the customer_id, customer_name, and respective sales figures back into the reporting table...and continue for each customer_id in the cursor resultset.

The set-based version:

DELETE FROM RewardsTable

INSERT RewardsTable (customer_id, customer_name, enrollment_date, sales_base, ytd_sales, percent_to_goal) SELECT customer_id, customer_name, cert_date, 0, 0, 0) FROM Customer WHERE ISDATE(cert_date) = 1

SELECT rt.customer_id CASE WHEN sum(inv.invoice_amount) IS NULL THEN 0 ELSE sum(inv.invoice_amount) END [sales_base] INTO #rt_sales_base FROM RewardsTable rt LEFT OUTER JOIN invoice_view inv WITH (NOLOCK) ON rt.customer_id = inv.customer_id WHERE inv.invoice_date >= rt.enrollment_date - 365 AND inv.invoice_date <= rt.enrollment_date

SELECT rt.customer_id CASE WHEN sum(inv.invoice_amount) IS NULL THEN 0 ELSE sum(inv.invoice_amount) END [ytd_sales] INTO #rt_ytd_sales FROM RewardsTable rt LEFT OUTER JOIN invoice_view inv WITH (NOLOCK) ON rt.customer_id = inv.customer_id WHERE inv.invoice_date >= rt.enrollment_date AND inv.invoice_date <= rt.enrollment_date + 365

UPDATE RewardsTable SET sales_base = tmp.sales_base FROM RewardsTable INNER JOIN #rt_sales_base tmp ON RewardsTable.customer_id = tmp.customer_id

UPDATE RewardsTable SET ytd_sales = tmp.ytd_sales FROM RewardsTable INNER JOIN #rt_ytd_sales tmp ON RewardsTable.customer_id = tmp.customer_id

DROP TABLE #rt_sales_base
DROP TABLE #rt_ytd_salesI did some additional testing. I populated an enrollement_date in for all customers such that all ~12000 customer records would be processed, and notice that the CURSOR-based stored procedure was no longer faster than the set-based one. So I now have a third question. At some point the dataset became large enough that the optimations chosed on the execution path passed up the cursor based solution. Is it fair to say that (if your not worried about running in a non-MS SQL environment) the CURSORS on smaller sets of data hold a performance advantage?

Wednesday, March 7, 2012

Performance of extended stored procedures in SQL Server 2000

What is the overhead of using extended stored procedures?

I created a table with 500,000 rows.
1) I ran a select on two columns and it runs in about 5 seconds.
2) I ran a select on one column and called an UDF (it returns a
constant string) and it takes 10 seconds.
3) I ran a select on one column and called a UDF that calls an extended
stored procedure that returns a string and it takes 65 seconds.
I also tried running test 3 with 4 concurrent clients and each client
takes about 120 seconds.(smauldin@.ingrian.com) writes:
> What is the overhead of using extended stored procedures?
> I created a table with 500,000 rows.
> 1) I ran a select on two columns and it runs in about 5 seconds.
> 2) I ran a select on one column and called an UDF (it returns a
> constant string) and it takes 10 seconds.
> 3) I ran a select on one column and called a UDF that calls an extended
> stored procedure that returns a string and it takes 65 seconds.
> I also tried running test 3 with 4 concurrent clients and each client
> takes about 120 seconds.

The overhead is apparently significant in this case. And it may well be
typical. By calling an extended stored procedure, you are completely
serializing the processing. If you rewrote the query as a cursor and
called the XP in the extednded stored procedure, you would probably
see a higher value than 65, but not that much higher.

Thus the overhead is not so much in the XP itself, as in the way
it affects the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp