How to catch stored procedure execution time?

Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:

Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.

ZYT

The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||Below batch query might help you to determine the performance in secounds

declare @.startproc datetime
declare @.endproc datetime
declare @.time integer

select @.startproc = getdate()

exec <stored procedure>
select @.endproc = getdate()

select @.time = DATEDIFF(second, @.startproc, @.endproc)

print str(@.time)|||Pat:

Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.

ZYT

Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!

Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.

I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!

As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).

The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!

-PatP|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.

Thank

ZYT

I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning

As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...

Logging the sproc call from code would not give you a true length due to other resources

I find that log so useful in so many ways

What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.

Well that's pure bull sheet

Blame it on profiler

How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:

By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.

Thanks

ZYT

No experience but Scenario 1in the paper describes exactly this.|||Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH|||Pootle:

I got it.

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)

can work. or refer http://support.microsoft.com/kb/270599.

Thanks everyone. This is a good post.

ZYT

Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH

how to calculate cost of a trigger?

I wanna used derived fields to improve select performance of my system.

How can I calculate the trigger cost in the system?

Triggers are resource intensive objects, you should look for alternatives before implementing them. You can run the profiler to find out the cost of any code you run through SQL Server. You will find the Profiler under tools in Management studio. Hope this helps.|||I wanna usedderived fields to improve select performance of my system.

How can I calculate the trigger cost in the system?

We are designing the DATA MODELand The TASK table it has two mutually exclusive fields.
WHOID > PersonID,OrganizationID
WHATID > DocumentID,EstateID,LeadID,RequirementID,

and on the Task UI we display the names of the Related EntityTypes and this causes us 10 inner/Joins to pull the data from database.

One of the designer in the team suggested putting derived fields such as
WHONAME > PersonName,OrganizationName
WHATNAME > DocumentName,EstateType,LeadName,RequirementName

So from performance perspective,We are really confused as putting triggers cause implicit locks on the system where as the other option causes 10 joins.

There is one to many relationship between WHOID and TASK(..3 related entitytypes mutually exclusive)
and also there is one to many relationship between WHATID and TASK(..7 related entitytypes mutually exclusive)

7 joins + 3 joins = 10 joins.

it is a really simple UI infact.
The Edit screen of TASK EDIT
--------------
Subject > char
FromDate > datetime
Todate > datetime
Priority > char
State > char
Email > char
Whoname> Related Person,Organization or Lead NAME
WhatName>Related Document,Estate,Requirement,Case,Oppurtunity Name ..etc

------
I would love not to have ten joins but the requirement forces us that way|||

What you need is simple DRI(declarative referenctial integrity) enabled on the tables in Management studio, so Updates will Cascade and if needed Deletes will Cascade. I know you need a DDL( data definition langauge) person in your team because a Table design comes down to files and association, while relationship is determined by upper and lower bound Cardinality. If you follow that most of your tables will disappear and what is now a JOIN will become a simple additional condition which in SQL Server can be added with the AND operator to a JOIN operation.

Some people do just DDL(data definition language) for a living. Run a search for DRI(declarative referential integerity) in SQL Server BOL (books online). Hope this helps.

|||

thanks caddre.that looks interesting.

http://www.cvalde.net/document/declaRefIntegVsTrig.htm

but I have a hard time understanding how it will change my WHONAME and WHATNAME.

if the it does it would be great.

|||

I could be wrong but what you have looks like objects to me because if those are two tables you need just two IDENTITY or it becomes a composite. Try the link below and download and install AdventureWorks 2005 most tables you need in a business application is in there. Look at the tables pay attention to the constraints and DRI (declarative referential integrity). Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||

Is there a problem with the 10 joins?

Personally, I would just create a view to simplify the select statement that has all your required joins in it. Just make sure your tables are indexed properly, and you shouldn't have an issue.

|||

Hmmm I have tested it with 10 million records and the derived approach is 20X faster.

I really don't wanna use triggers but for now I'll just try the links caddre send.

|||

If you aren't displaying all 10 million rows at a time within the UI, you may find it beneficial to do your limiting select first, then doing the 10 joins once your have your limited resultset back, kinda like:

SELECT ...
FROM (Your limited query here)
JOIN ...
JOIN ...
JOIN ..

although why SQL Server isn't already doing that for you is odd. A bad query plan, or inaccurate statistics most likely. Before spending the time on trying to work around SQL, I'd first run your query through the tuning wizard and see what it comes up with, you may be suprised. If you've already done that, my appologies.

You want to try an indexed view approach as well, which may give you what you are looking for. Something like:

CREATE MyView WITH SCHEMABINDING AS

SELECT (Your 10 fields here),t1.Name,t2.Name,t3.Name,t4.Name,...
FROM MyTable mt
JOIN EntityRef t1 ON (mt.key=t1.key)
...

Then create a primary key on the view, then create your indexes (Whatever you are limiting/searching on)

You'll incur much of the same overhead as triggers though, so test your results.

|||

As a side note, Caddre has mentioned a lot of differing technologies, one of which is DRI. DRI by itself won't help to solve your issue, but by leveraging it, if possible, it could reduce the complexity of your database design, and increase your performance.

For example, take table1, and table2 here:

Table Name > Fields

Table1>EntityID,col1,col2,col3,col4...
Table2>EntityID,Name

Normally DRI is used to link both entityIDs in table1 and table2 in a foreign key relationship. This by itself will not gain you any performance at all, infact in many operations it will decrease overall performance substantially. However, if you redesign your tables, like so:

Table1>Name,col1,col2,col3,col4...
Table2>Name

Now by using DRI to maintain the relationship between Name (via a foreign key), then it's quite possible you will see a performance increase since you have just eliminated a join in your selects. Foreign keys can be used to "replicate" changes in the name field done in table2 to all records in table1 that use that name Now that's one VERY expensive update, but, it does insure that all instances in table1 that link to a particular name all use the exact same name. This is usually why you use identifiers in your base tables to begin with, and since those changes are very infrequent (Or should be), it's a decent trade off in performance, but you can make a pretty big mess of your database structure (and data) by taking this route if you aren't careful though. These in SQL Server are also called Cascading Deletes and Updates.

Using our above new schema, if you issue this:
UPDATE table2 SET Name='new value' where Name='old value'
SQL Server will actually do something like this:
BEGIN TRANSACTION
UPDATE table2 SET Name='new value' where Name='old value'
UPDATE table1 SET Name='new value' where Name='old value'
COMMIT TRANSACTION

With better error handling of course. If there are any errors, it's all rolled back, but it's called cascading be cause a delete in table2 would delete some records in table1, which in turn may delete some records in table3, etc.

Now with all that out of the way, you have to realize by replacing your small little ID field with a probably much larger field, sure you lose the join, but you've increased your row size. Indexes will be larger, and pages will contain less rows. Operations that require to scan large portions of your table will take longer, as well. So test a lot, on not just on what you need to do now, but think and test the ramifications on all your other queries as well.

|||

I have thought another method today which might be wrong ...instead of using DRI and triggers to maintain the data I am thinking about writing a class just for the purpose of putting data and updating data into my derived field.And I will tell from code behind that I need to update in anycase the update happens in my main tables of derived coloumns.

The advantages of this approach over triggers.

1.No implicit transactions

2.easy to debug

3.Faster than triggers

4.No unneccessary updates

5.Could write code to maintain and check the alldata.

Disadvantages I see

1.Triggers are more safe in terms of integrity.

2.Less chance to forget to update the derived fields.

*I am really confused some how but the requirement of the application forces us not to use three things that will load the SQL CPU load.

A:Triggers

B.UNIONS,Exits

C.Joins

|||

I fail to see how most of what you've listed as "advantages" are advantages. I would say:

Advantages:
1. No implicit transactions (All SQL Statements are an implied transaction, you haven't eliminated them, just simplified it -- But that's an advantage still)
2. Easy to debug -- For your application, if it's the only one accessing the data, yes.
3. Faster than triggers -- I doubt it. Sounds like you are going to have to make an extra round trip between the server and client to pull back the name field that you want to put in the field, which will make it many times slower, but holding locks for slightly less time. Unless you are caching the table(s) the 10 joins are getting their names from, in which case, you need to add the added application/web server memory cost as a disadvantage as well.
4. No unneccessary updates.
5. Could write code to maintain and check the alldata. -- This is not an advantage. It's a solution to the problem caused by the derived fields. It should be listed under disadvantages as "Have to write code to maintain and check the alldata".

Disadvantages:
1. Less safe in terms of integrity than triggers.
2. Possible data out of sync.
3. Increased application code, and extra logic that must be placed into every application that might need to update the data (or each routine within your application).
4. Have to write code to periodically maintain and check database integrity.

--

Please look into the indexed views I mentioned earlier, which doesn't suffer from the integrity issues, doesn't require more application logic (Other than you select from one view, and update a base table, but you're doing that from stored procedures now anyhow, right?) If the indexed views don't work for you, you could also use a BEFORE UPDATE/INSERT trigger, which aren't the nicest thing in the world to write, but keeps many of your listed advantages, and eliminates the disadvantages. (Guaranteed database integrity + no unneccessary updates + smaller lock time + faster than a client-side lookup)

As for your confusion on what you shouldn't use... (Triggers, UNIONS, Exits (did you mean EXISTS?), and joins)... Sounds like you've gotten information from someone who fell in love with a dumb record manager from the 1980's (Paradox, BTrieve, ISAM, or MySQL -- granted the latter is evolving beyond it's dumb record manager roots).

|||

I agree with Motley, because there is a reason the Object world have not created a replacement for Peter Chen 1976, the math that gave us what is now DRI(declarative referential integrity). I think you will run into data integrity problems as your application grow.

But the last part of your post is ok except JOINS because INNER JOINS with the AND operator will not eat up your CPU if you use management studio to show you execution cost and use the Profiler to optimize the cost of your SQL statement.

|||

indexed views > I would Love to use them butif I am not mistaken they have lots of rules to follow and I got to buy the enterprise editon and the 4 CPU price of SQL is4 X 20.000$where the standart edition is 4 X 6.000 USD for web applications.

for now I 'll be taking the risk of replication data inorder to have performance.

*I prefered to have a normalized and a clean data model where I can reuse the data logic without dublicating it.Pratically I could not find a way out for that in the physical design.

**Faster than triggers --> I might use threads.(which might mean more trouble but...)

|||

You are mistaken. They work in standard edition (Even SQL Express/MSDE), however, you must name the view directly. In enterprise edition if you have an indexed view, and a select/update doesn't mention the view, but it would benefit from it, it will automatically use it.

However, you are correct, there are some limitations, one of which is no LEFT JOINS, and no UNION. Just do them later (In another view that bases itself off the indexed view and left joins/unions with other data).

The nice part is, you can build it, test it, and see if it'll fit your needs in a matter of minutes.