January 20, 2015 at 7:55 pm
Hallo All,
I have an issue in my production with long running query .. After analysing the index , I decided to add 1 non clustered index with 16 include column but then my team is afraid it will impact the performance of insert into Table A ( for example) . At the moment it took a long time for insert because it is run many many time
How do I measure the Duration ( using SQL Profiler ) of that INSERT ?
I have run the trace in SQL PROFILER and I am confused with the duration showing in the SQL Profiler ..
if I run the trace for SELECT statement, it is clear , for example Duration : 1000 ms but in Insert it only show 1 or 0 and it shows many INSERT
Please kindly help. Much appreciate it !
Thanks a lot
January 20, 2015 at 9:39 pm
WhiteLotus (1/20/2015)
Hallo All,I have an issue in my production with long running query .. After analysing the index , I decided to add 1 non clustered index with 16 include column but then my team is afraid it will impact the performance of insert into Table A ( for example) . At the moment it took a long time for insert because it is run many many time
How do I measure the Duration ( using SQL Profiler ) of that INSERT ?
I have run the trace in SQL PROFILER and I am confused with the duration showing in the SQL Profiler ..
if I run the trace for SELECT statement, it is clear , for example Duration : 1000 ms but in Insert it only show 1 or 0 and it shows many INSERT
Please kindly help. Much appreciate it !
Thanks a lot
WhiteLotus,
By default the duration column is displayed in milliseconds even though the value under the hood is microseconds. As far as it displaying many inserts, I would venture to guess that you have the SP:StmtCompleted column in your EventClass, which may be is showing each insert statement into your indexes as well. Any time you insert data that is involved any one of the indexes, you will get an insert for that index as well as an insert into the table itself.
It might be helpful if you posted your query and execution plan as well as the definitions for existing indexes. I am surprised that you would want to create an index with one key column and 16 non-key columns. I am wondering if we can come up with something else that might be a better index solution.
January 21, 2015 at 1:41 am
lmarkum (1/20/2015)
Any time you insert data that is involved any one of the indexes, you will get an insert for that index as well as an insert into the table itself.
Err, no you don't.
Statement completed in Profiler shows just that, the end of a T-SQL statement. If the statement is an insert, then profiler shows one statement completed event with the insert in the text data regardless of whether there's one index or 400 indexes.
You'll see statements in triggers, but you will not see the internal operations of the insert. When there are indexes, the inserts into the table and the indexes are all part of one single statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2015 at 1:46 am
WhiteLotus (1/20/2015)
How do I measure the Duration ( using SQL Profiler ) of that INSERT ?
Same way as any other statement. There's nothing special about inserts
if I run the trace for SELECT statement, it is clear , for example Duration : 1000 ms but in Insert it only show 1 or 0 and it shows many INSERT
If you're seeing multiple inserts, then multiple inserts are running. Either you're doing inserts in a loop, or the trace isn't filtered and you're seeing inserts from other sessions too. It's a little hard to answer as there's not enough details. Can you at least post the code that does the inserts?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2015 at 7:57 pm
Hi All ,
Thanks for the reply .. Really appreciate it !
The query that I run is :
SELECT
''"'' + oc.[name] + ''"'' AS ''"name"''
,''"'' + ll.[str_Desc] + ''"'' AS ''"str_Desc"''
,''"'' + RTRIM(cast (ele.[locationid] AS CHAR)) + ''"'' AS ''"byt_HomeLocationID"''
,''"'' + el.[str_ESN] + ''"'' AS ''"str_ESN"''
,''"'' + ele.[LogType] + ''"'' AS ''"LogType"''
,''"'' + ele.[appV] + ''"'' AS ''"appV"''
,''"'' + ele.[BspV] + ''"'' AS ''"BspV"''
,''"'' + ele.[BNumber] + ''"'' AS ''"BNumber"''
,''"'' + ele.[EventSense] + ''"'' AS ''"EventSense"''
,''"'' + ele.[ComponentType] + ''"'' AS ''"ComponentType"''
,''"'' + ele.[DNumber] + ''"'' AS ''"DNumber"''
,''"'' + CONVERT(varchar,ele.[Timestamp],103) + '' '' + CONVERT(varchar,ele.[Timestamp],108) + ''"'' AS ''"Timestamp"''
,''"'' + replace(ele.[Message],char(10),'''') + ''"'' AS ''"message"''
,''"'' + ele.[MessageLevel] + ''"'' AS ''"MessageLevel"''
,''"'' + ele.[EventCode] + ''"'' AS ''"EventCode"''
,''"'' + isnull(ele.[EventSubType],''Null'') + ''"'' AS ''"EventSubType"''
,''"'' + isnull(ele.[GPSLocation],0) + ''"'' AS ''"GPSLocation"''
FROM [A].[dbo].[LogEntries] ele
inner join [A].[dbo].[EquipmentList] el on ele.EquipmentID = el.int4_EquipmentID
inner join [A].[dbo].[OperatingCompany_Location] ocl on ele.locationid = ocl.locationId
inner join [A].[dbo].[operatingCompanies] oc on ocl.OperatingCompanyId = oc.Id
join A.dbo.Location ll on ele.locationid = ll.byt_LocationID
where '''+@period_start+''' <= cast(DatelastUpdated as DATE) AND cast(DatelastUpdated as DATE) < '''+@period_end+'''
and the INSERT that is running everynite for a few hours is as below :
exec sp_executesql N'INSERT INTO Table A (ComponentState, Component, LogType, AppVersion, BspVersion, BNumber, EventSense, ComponentType, DeviceNumber, Timestamp, Message, MessageLevel, EventCode, EventSubType, GPSLocation, DateLastUpdated, EquipmentId, LocationId)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17); select SCOPE_IDENTITY()',N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 datetime,@p10 nvarchar(4000),@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 nvarchar(4000),@p14 nvarchar(4000),@p15 datetime,@p16 int,@p17 int',@p0=N'Inoperable',@p1=N'TicketingApp',@p2=N'EventsLog',@p3=N'1.8.1403.30',@p4=N'25.1.1',@p5=N'052386',@p6=N'Set',@p7=N'CubicSydneyBV',@p8=N'1',@p9='2015-01-22 09:05:55',@p10=N'Fault Reported',@p11=N'Error',@p12=N'506',@p13=NULL,@p14=NULL,@p15='2015-01-23 02:13:45',@p16=4073,@p17=20
January 23, 2015 at 1:28 am
Couple comments immediately
Why the dynamic SQL? All you're doing there is making things more complex, there's nothing in that query I can see that requires dynamic SQL.
SELECT oc.[name] AS name,
ll.[str_Desc] AS str_Desc,
RTRIM(CAST (ele.[locationid] AS CHAR)) AS byt_HomeLocationID,
el.[str_ESN] AS str_ESN,
ele.[LogType] AS LogType,
ele.[appV] AS appV,
ele.[BspV] AS BspV,
ele.[BNumber] AS BNumber,
ele.[EventSense] AS EventSense,
ele.[ComponentType] AS ComponentType,
ele.[DNumber] AS DNumber,
CONVERT(VARCHAR, ele.[Timestamp], 103) + ' ' + CONVERT(VARCHAR, ele.[Timestamp], 108) AS Timestamp,
REPLACE(ele.[Message], CHAR(10), '') AS message,
ele.[MessageLevel] AS MessageLevel,
ele.[EventCode] AS EventCode,
ISNULL(ele.[EventSubType], 'Null') AS EventSubType,
ISNULL(ele.[GPSLocation], 0) AS GPSLocation
FROM [A].[dbo].[LogEntries] ele
INNER JOIN [A].[dbo].[EquipmentList] el ON ele.EquipmentID = el.int4_EquipmentID
INNER JOIN [A].[dbo].[OperatingCompany_Location] ocl ON ele.locationid = ocl.locationId
INNER JOIN [A].[dbo].[operatingCompanies] oc ON ocl.OperatingCompanyId = oc.Id
JOIN A.dbo.Location ll ON ele.locationid = ll.byt_LocationID
WHERE @period_start <= CAST(DatelastUpdated AS DATE)
AND CAST(DatelastUpdated AS DATE) < @period_end
The where clause predicates ensure no index usage, due to the CAST on the column
@period_start <= CAST(DatelastUpdated AS DATE) AND CAST(DatelastUpdated AS DATE) < @period_end
Hence you're probably looking at multiple table scans, which are not going to be fast.
The insert you posted will insert a single row and it will show up as a single statement completed event in profiler.
If you're seeing multiple events, then the insert is running multiple times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply