December 5, 2008 at 7:17 am
Hi, I'm trying to optimize a few queries that are sent to the Sql Server when opening a product. There is a lot of queries, fast and less fast that are launched, and that is just fine.
I came across a few queries that took 1 ms to execute (SP:StmtCompleted), but had a RPC:Completed value of about 2000 ms.
I'm guessing the real completion time, calling + executing + retrieving is more like 2000 ms, but, why is it so long to retrieve the data itself when there is no row retrieved by the query. I find it weird that a query running in a few ms (1 to 8), cannot be retrieved in under 2000 ms.
Is there anything I can do to optimize this, or understand more what is the difference between those 2 trace details.
Thanks in advance,
J-F
Cheers,
J-F
December 5, 2008 at 7:21 am
SP:StmtCompleted shows each step in a stored procedure, like IF @parameter is Null Then, while RPC:Completed and SP:Completed events are fired when an entire RPC call or Stored Procedure is completed. Most of the the time SP:StmtCompleted should be fast.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2008 at 7:23 am
Ok, but there is no Stored Procedure, it is only a query that is sent using the VB.net Application. I know that a lot of small StmtCompleted can make a RPC call longer, but the query is simply alone in this case.
So 1 small query running in 1 to 8 ms takes 2000ms to complete the RPC call?
Cheers,
J-F
December 5, 2008 at 7:28 am
What is in the TextData column of the SP:StmtCompleted events and the RPC:Completed events. Are you sure that they are related?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2008 at 7:33 am
Yes, they are related, it is the query completion, and RPC call contains only the query :
This is the Stmt Completed, which finished in 1ms, and returned 0 rows:
SELECT * FROM
(
SELECT dc.Categoryid,
dc.CategoryName,
dc.CategoryDescription
FROM GICSPF..GICSPFVendorCategoryHeader hc
INNER JOIN GICSPF..GICSPFVendorCategoryDetail dc
ON hc.CompanyID = dc.CompanyID
AND hc.DivisionID = dc.DivisionID
AND hc.DepartmentID = dc.DepartmentID
AND hc.VendorCategoryID = dc.VendorCategoryID
WHERE hc.CompanyID = @CompanyID
AND hc.DivisionID = @DivisionID
AND hc.DepartmentID = @DepartmentID
AND hc.VendorID = @VendorID
) AS D
And this is the textdata of the RPC Completed, which completed in 2009 ms, and returned 0 rows:
exec sp_executesql N'
SELECT * FROM
(
SELECT dc.Categoryid,
dc.CategoryName,
dc.CategoryDescription
FROM GICSPF..GICSPFVendorCategoryHeader hc
INNER JOIN GICSPF..GICSPFVendorCategoryDetail dc
ON hc.CompanyID = dc.CompanyID
AND hc.DivisionID = dc.DivisionID
AND hc.DepartmentID = dc.DepartmentID
AND hc.VendorCategoryID = dc.VendorCategoryID
WHERE hc.CompanyID = @CompanyID
AND hc.DivisionID = @DivisionID
AND hc.DepartmentID = @DepartmentID
AND hc.VendorID = @VendorID
) AS D
',N'@CompanyID nvarchar(27),@DivisionID nvarchar(4),@DepartmentID nvarchar(4),@VendorID nvarchar(4000)',@CompanyID=N'Guillevin International Co.',@DivisionID=N'9820',@DepartmentID=N'2028',@VendorID=NULL
It is the same exact query, but with the parameters passed in.
How is it possible?
Cheers,
J-F
December 5, 2008 at 7:35 am
StmtCompleted is pretty expensive to the system. You'd be better off collecting RPC:Completed and SQL:BatchCompleted. Those two are more or less equal. StmtCompleted are the individual statements within a batch or remote procedure call.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2008 at 7:52 am
I ran a test. I saw two StmtCompleted events for a proc that called sp_executesql. What I saw was a preperation statement and an execution statement. I suspect you'll see that there's a second statement in there somewhere that took 9000ms.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2008 at 8:08 am
When i look closely at the trace events I see a Stmt Completed, but just before that, I see a "Starting Up Database". I don't see the time it took to start the database, but would that be why it was slower?
And how come it started the database twice, because the next instruction is exactly the same, and it started the database a second time, it's the same database.
J-F
Cheers,
J-F
December 5, 2008 at 8:16 am
Do you have the database set to AutoClose? That should be turned off if it is on.
Are you tracing the starting and completed events? That would explain seeing the data twice.
Can you save the trace as a file and attach it to the thread?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2008 at 8:25 am
I reran the same query, and it now runs in 1 ms, the stmt and the RPC call.
I guess the database needed to start, maybe because I restored the database. I thought every database was automatically started when starting the service, I guess I was wrong.
Well, thanks for the help, the product is now loads much faster.
J-F
Cheers,
J-F
December 8, 2008 at 7:57 am
I still have a weird problem with this. The database keeps starting. It takes 2 secs for the database to start, and if I look at the log closely, everytime the product screen launches a query, the database says
"Eventlog : Starting up Database ........."
The program uses the name of the database to access it.
Let's say we have the default database which is "Enterprise", we also have a database that is called "GICSPF", which is the database with the problem.
When the client application calls the "GICSPF" database, it does something like:
USE enterprise
Select * from SomeEnterpriseTable e inner join GICSPF.dbo.SomeGICSPFTable s
on e.Key = s.Key
There are a few queries that are launched from the application to load the product screen, and everytime we launch a query that implicates the GICSPF database, there is an eventLog saying "Starting Up Database GICSPF".
The database is NOT set to Autoclose, I checked to be sure.
We found a workAround, which is not cool at all, I need to Open a SSMS, connect to GICSPF, and do nothing. I'm guessing a connection needs to be opened to GICSPF so we can access it without it starting it all the time.
This is really annoying, because we have the product screen opening in either 1-3 secs when the database GICSPF is active, or about 26 seconds when its not.
Can anyone think of a solution?
Thanks,
J-F
Cheers,
J-F
December 8, 2008 at 8:03 am
Is it possible the database is being closed by the app when it closes it's connection? That's possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 8, 2008 at 8:08 am
This is a possibility, but I checked with the programmers, and they said they never close a database after use, they only close the connection. And the program uses a central connection for every client connected to the server, so there should be no closing at all.
Cheers,
J-F
December 8, 2008 at 8:10 am
Something is shutting down your database. If it's not AUTO_CLOSE, it's something else. Code sure seems like the next best bet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 8, 2008 at 8:28 am
I agree with Grant, something is closing the database. You should put a trace on it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply