March 22, 2017 at 10:21 am
Guys I would like to know in SQL Server how I get specific performance numbers. For example, how long did it take SQL server to execute a certain sql statement? Overall, I want to be able to track performance at the db level and I guess the overall SQL Server level.
March 22, 2017 at 11:01 am
mstephens1754 - Wednesday, March 22, 2017 10:21 AMGuys I would like to know in SQL Server how I get specific performance numbers. For example, how long did it take SQL server to execute a certain sql statement? Overall, I want to be able to track performance at the db level and I guess the overall SQL Server level.
One way you may want to get started is by downloading Glenn Berry's diagnostic queries. You can find the latest scripts for different versions of SQL Server at this link:
SQL Server Diagnostic Information Queries
Sue
March 22, 2017 at 12:30 pm
I need assistance in a SQL that has a performance issue . The query is running too long. There is a Table: Archive with Validated addresses with 1,063,349 rows. There is a row for Property Address and another row for Mailing Address and another row if the property address and mailing address is the same. There fore the table will have a property address row with Address type: P and a mailing address row with Address Type: M or Both property and mailing with an address type: B There is another table: Property with 21,413 Rows. The two tables: Property and Archive are joined to extract any missing Validated Addresses on the table: Archive. Missing implies that there is no validated address on the table: Archive. When I am using the ISNULL the query runs for ever. The query is below. The bold code (WHERE DtlProperty.PolicyNumber is null) I believe is where the problem is.
With DtlProperty (PolicyNumber)
As
(SELECT
Archive.PolicyNumber
FROM CLUEReporting.dbo.PropertyAddress PropertyAddress
Left Join CLUEReporting.dbo.CLUE_Archive Archive On (Archive.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE Archive.PolicyNumber = PropertyAddress.PolicyNumber
AND Archive.Street = PropertyAddress.LocAddress1
AND Archive.Street2 = PropertyAddress.LocAddress2
AND Archive.City = PropertyAddress.LocCity
AND Archive.State = PropertyAddress.LocState
),
DtlMailing (PolicyNumber)
As
(SELECT
Archive.PolicyNumber
FROM CLUEReporting.dbo.PropertyAddress PropertyAddress
Left Join CLUEReporting.dbo.CLUE_Archive Archive On (Archive.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE Archive.PolicyNumber = PropertyAddress.PolicyNumber
AND Archive.Street = PropertyAddress.bioAddress1
AND Archive.Street2 = PropertyAddress.bioAddress2
AND Archive.City = PropertyAddress.bioCity
AND Archive.State = PropertyAddress.bioState
AND Archive.Zip = PropertyAddress.bioZipCode
)
--Property Address and Mailing Address
SELECT DISTINCT
PropertyAddress.PolicyNumberAS PolicyNumber,
PropertyAddress.PreviousPolicyIDAS PreviousPolicyID,
''AS ClaimID,
'"' + PropertyAddress.LocAddress1 + '"'AS Street,
'"' + PropertyAddress.LocAddress2 + '"'AS Street2,
'"' + PropertyAddress.LocCity + '"'AS City,
PropertyAddress.LocStateAS State,
PropertyAddress.LocZipCodeAS Zip,
'B'AS AddressType,
PropertyAddress.DATABASEPOLICYAS DATABASEPOLICY,
PropertyAddress.ENTRYDATEAS ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress
Left Join DtlProperty DtlProperty On (DtlProperty.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE DtlProperty.PolicyNumber is null
AND PropertyAddress.PolicyNumber IN
(SELECT
PolicyNumber
FROM CLUEReporting.dbo.PropertyAddress
WHERE PropertyAddress.LocAddress1 = PropertyAddress.bioAddress1
AND PropertyAddress.LocAddress2 = PropertyAddress.bioAddress2
AND PropertyAddress.LocCity = PropertyAddress.bioCity
AND PropertyAddress.LocState = PropertyAddress.bioState
AND PropertyAddress.LocZipCode = PropertyAddress.bioZipCode)
UNION ALL
--Property Address
SELECT DISTINCT
PropertyAddress.PolicyNumberAS PolicyNumber,
PropertyAddress.PreviousPolicyIDAS PreviousPolicyID,
''AS ClaimID,
'"' + PropertyAddress.LocAddress1 + '"'AS Street,
'"' + PropertyAddress.LocAddress2 + '"'AS Street2,
'"' + PropertyAddress.LocCity + '"'AS City,
PropertyAddress.LocStateAS State,
PropertyAddress.LocZipCodeAS Zip,
'P'AS AddressType,
PropertyAddress.DATABASEPOLICYAS DATABASEPOLICY,
PropertyAddress.ENTRYDATEAS ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress
Left Join DtlProperty DtlProperty On (DtlProperty.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE DtlProperty.PolicyNumber is null
AND PropertyAddress.PolicyNumber IN
(SELECT
PolicyNumber
FROM CLUEReporting.dbo.PropertyAddress
WHERE (PropertyAddress.LocAddress1 != PropertyAddress.bioAddress1
OR PropertyAddress.LocAddress2 != PropertyAddress.bioAddress2
OR PropertyAddress.LocCity != PropertyAddress.bioCity
OR PropertyAddress.LocState != PropertyAddress.bioState
OR PropertyAddress.LocZipCode != PropertyAddress.bioZipCode))
UNION ALL
--Mailing Address
SELECT DISTINCT
PropertyAddress.PolicyNumberAS PolicyNumber,
PropertyAddress.PreviousPolicyIDAS PreviousPolicyID,
''AS ClaimID,
'"' + PropertyAddress.bioAddress1 + '"'AS Street,
'"' + PropertyAddress.bioAddress2 + '"'AS Street2,
'"' + PropertyAddress.bioCity + '"'AS City,
PropertyAddress.bioStateAS State,
PropertyAddress.bioZipCodeAS Zip,
'M'AS AddressType,
PropertyAddress.DATABASEPOLICYAS DATABASEPOLICY,
PropertyAddress.ENTRYDATEAS ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress
Left Join DtlMailing DtlMailing On (DtlMailing.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE DtlMailing.PolicyNumber is null
AND PropertyAddress.PolicyNumber IN
(SELECT
PolicyNumber
FROM CLUEReporting.dbo.PropertyAddress
WHERE (PropertyAddress.LocAddress1 != PropertyAddress.bioAddress1
OR PropertyAddress.LocAddress2 != PropertyAddress.bioAddress2
OR PropertyAddress.LocCity != PropertyAddress.bioCity
OR PropertyAddress.LocState != PropertyAddress.bioState
OR PropertyAddress.LocZipCode != PropertyAddress.bioZipCode))
March 22, 2017 at 12:52 pm
Do you guys also use SQL server monitoring tools such as Red Gate
March 22, 2017 at 1:01 pm
We have no SQL monitoring tools. This is a small shop
March 22, 2017 at 1:03 pm
i think your performance problem is probably the multiple OR statements; that's likely causing a table scan. WHERE ( [PropertyAddress].[LocAddress1] != [PropertyAddress].[bioAddress1]
OR [PropertyAddress].[LocAddress2] != [PropertyAddress].[bioAddress2]
OR [PropertyAddress].[LocCity] != [PropertyAddress].[bioCity]
OR [PropertyAddress].[LocState] != [PropertyAddress].[bioState]
OR [PropertyAddress].[LocZipCode] != [PropertyAddress].[bioZipCode]
)
since you really just want to know where ANY of them are different, you could use somEthing like this instead IN THE two places in your query where you have stacked OR statements.WHERE CHECKSUM(
[PropertyAddress].[LocAddress1],
[PropertyAddress].[LocAddress2],
[PropertyAddress].[LocCity] ,
[PropertyAddress].[LocState],
[PropertyAddress].[LocZipCode])
<> CHECKSUM(
[PropertyAddress].[bioAddress1],
[PropertyAddress].[bioAddress2]
[PropertyAddress].[bioCity],
[PropertyAddress].[bioState],
[PropertyAddress].[bioZipCode] )
Lowell
March 22, 2017 at 3:52 pm
dangelo211 - Wednesday, March 22, 2017 1:01 PMWe have no SQL monitoring tools. This is a small shop
Redgate SQL Monitor is absolutely designed (and priced) with small shops in mind.
Just sayin'
DISCLOSURE: I work for Redgate.
"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
March 22, 2017 at 3:59 pm
If you want to monitor your queries for performance metrics and you really care about individual calls to those queries, not simply an aggregate average of calls, then the best method, since we're talking SQL Server 2012, is to use Extended Events. This will get you started.
I like Lowell's approach to the OR clauses (actually, Lowell is one of those people you should just listen to in general).
I think another performance issue may be coming from the use of the DISTINCT clause. That's an aggregation function and could certainly be negatively impacting performance, especially since you're using it repeatedly. Frequently DISTINCT is an indicator that there may be problems with the data or the structure of the data, or possibly the structure of the query, since you use it to attempt to eliminate duplicates where none should be. I'd focus there to see if you can eliminate the use of the DISTINCT clause.
Finally, to understand how a query is being resolved in the optimizer, you should look at the execution plan. To get started on that, look at the book in my signature. There's a free download of it somewhere around here.
"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
March 23, 2017 at 3:51 am
Hi,
can you tell us something about your environment? How much RAM, CPU, HDD configuration. Maybe your hardware is not big/strong enough?
Kind regards,
Andreas
March 23, 2017 at 8:25 am
I am running SQL 2012 Enterprise on Windows 2012 R2. The box is virtual the system contains 32 gigs of ram with 24 dedicated to SQL Server. Also it has 4 cores dedicated to it Zeon E5 2650 2.6GHZ. The server host several databases the database in question is one that we use for an e commerce application. We are seeing some strange things with the ecommerce db for example we will see dead locks. So my first question whats the best way to track down a dead lock and see what sql code was being executed at the time of the lock. My second problem is I will get stack trace errors such as the one below. I am trying to figure out what happened to the transaction overall I want to make sure that the database side of things is not causing any problems.
March 23, 2017 at 8:46 am
Deadlocks are best investigated in a 2012 or greater server through the system_health extended event session which is actively running on all your servers by default. Here's an article from Microsoft to get you started on system_health. Here are several articles on dealing with deadlocks by Jonathan Kehayias.
As to the stack trace, do you also have errors of some kind on the database server error log? It's possible that the stack trace may just be reporting standard errors that aren't being handled appropriately by the app. It might even be related to the deadlocks.
"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
March 23, 2017 at 10:18 am
In regards to the trace I don't see anything in the SQL server logs. I guess what I'm trying to say is how does that stack trace relate to SQL server what should I be looking for to see if it is a SQL server problem. For example, when this happens is there a process id that goes with it if so can you track down what that process id was doing, Sorry if I' making zero sense I don't have much experience in tracking down these types of problems
March 23, 2017 at 10:37 am
dangelo211 - Wednesday, March 22, 2017 12:30 PMI need assistance in a SQL that has a performance issue . The query is running too long. There is a Table: Archive with Validated addresses with 1,063,349 rows. There is a row for Property Address and another row for Mailing Address and another row if the property address and mailing address is the same. There fore the table will have a property address row with Address type: P and a mailing address row with Address Type: M or Both property and mailing with an address type: B There is another table: Property with 21,413 Rows. The two tables: Property and Archive are joined to extract any missing Validated Addresses on the table: Archive. Missing implies that there is no validated address on the table: Archive. When I am using the ISNULL the query runs for ever. The query is below. The bold code (WHERE DtlProperty.PolicyNumber is null) I believe is where the problem is.With DtlProperty (PolicyNumber)
As
()
Please note that you have hijacked a live thread posted by another user.
There's a fast query hiding inside this query. Something along these lines:
SELECT
p.PolicyNumber,
p.PreviousPolicyID,
[ClaimID] = '',
[Street] = '"' + CASE WHEN x.AddressType = 'M' THEN p.bioAddress1 ELSE p.LocAddress1 END + '"',
[Street2] = '"' + CASE WHEN x.AddressType = 'M' THEN p.bioAddress2 ELSE p.LocAddress2 END + '"',
[City] = '"' + CASE WHEN x.AddressType = 'M' THEN p.bioCity ELSE p.LocCity END + '"',
[State] = CASE WHEN x.AddressType = 'M' THEN p.bioState ELSE p.LocState END,
[Zip] = CASE WHEN x.AddressType = 'M' THEN p.bioZipCode ELSE p.LocZipCode END,
x.AddressType,
p.DATABASEPOLICY,
p.ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress p
LEFT JOIN CLUEReporting.dbo.CLUE_Archive DtlProperty
ON DtlProperty.PolicyNumber = p.PolicyNumber
AND DtlProperty.Street = p.LocAddress1
AND DtlProperty.Street2 = p.LocAddress2
AND DtlProperty.City = p.LocCity
AND DtlProperty.State = p.LocState
LEFT JOIN CLUEReporting.dbo.CLUE_Archive DtlMailing
ON DtlMailing.PolicyNumber = p.PolicyNumber
AND DtlMailing.Street = p.bioAddress1
AND DtlMailing.Street2 = p.bioAddress2
AND DtlMailing.City = p.bioCity
AND DtlMailing.State = p.bioState
AND DtlMailing.Zip = p.bioZipCode
CROSS APPLY (
SELECT [AddressType] = CASE
WHEN LocAddress1 = bioAddress1 AND LocAddress2 = bioAddress2 AND LocCity = bioCity AND LocState = bioState AND LocZipCode = bioZipCode
AND DtlProperty.PolicyNumber IS NULL
THEN 'B'
WHEN (LocAddress1 != bioAddress1 OR LocAddress2 != bioAddress2 OR LocCity != bioCity OR LocState != bioState OR LocZipCode != bioZipCode)
AND DtlProperty.PolicyNumber IS NULL
THEN 'P'
WHEN (LocAddress1 != bioAddress1 OR LocAddress2 != bioAddress2 OR LocCity != bioCity OR LocState != bioState OR LocZipCode != bioZipCode)
AND DtlMailing.PolicyNumber IS NULL
THEN 'M'
ELSE NULL END
) x
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2017 at 11:26 am
Nuts. Sorry Chris. I didn't realize that wasn't the OP reporting a different problem. My apologies.
"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
March 23, 2017 at 11:35 am
mstephens1754 - Thursday, March 23, 2017 10:18 AMIn regards to the trace I don't see anything in the SQL server logs. I guess what I'm trying to say is how does that stack trace relate to SQL server what should I be looking for to see if it is a SQL server problem. For example, when this happens is there a process id that goes with it if so can you track down what that process id was doing, Sorry if I' making zero sense I don't have much experience in tracking down these types of problems- Stack Trace: System.Data.Entity.Infrastructure.CommitFailedException:An error was reported while committing a database transaction but it could notbe determined whether the transaction succeeded or failed on the databaseserver.
So, it might not be a SQL Server "problem." Just off the top of my head I can explain this two ways. First, this system was involved in a deadlock. When a deadlock occurs, one of the processes is chosen as a victim and the transaction is rolled back. If you're not trapping for transaction errors in your Entity Framework (pretty sure that's an EF statement there), you'll get the error you see. However, it could also be something more benign that won't even show up on the database servers error log. Let's assume again that you're not trapping for failed transactions on the client (a safe bet based on this error) and you attempt to insert data to a table with a foreign key constraint, but your data is incorrect. It'll rollback that transaction on the database, but no errors are logged locally.
To really troubleshoot this, I'd do one of two things. Get the datetime when the stack trace error was generated and see if that corresponds to a deadlock. If yes, you're done. If not, you're stuck for the moment because, by default, there's no extended event session capturing all query executions, so there's nothing to match the error to. To do this, you'll want to set up an extended events session and capture rpc:started and sql batch started along with completed for each. As part of setting it up, make sure that you enable the ability to track the related events. Then, you're looking for a starting event that has no closing event and corresponds in time with the next time (yeah, you'll have to wait till the error occurs again) the stack trace.
"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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply