Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
SantuSantu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:

> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>
>

Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
Santu
Santu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>
|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:

> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>
>

Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
SantuSantu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:
> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
> >I have a server with just 4 databases,
> > the issue is that the sql server does not perform well. In the task
> > manager
> > it occupies the maximum mount of memory in the system, and maximum cpu
> > time,
> >
> > the database is just a few gb's(10 -15) and the server has 1 gb ram,
> >
> > and especially if i use a reporting tool made in vb as to query the
> > database
> > it works exceptionally slow,
> >
> > one of my guys says it bcoz of a utility we have working, it bring data
> > from
> > another server connected to our network via an IPLC link(2mb link), this
> > utility works on just 2 tables, brings data from the table up there and
> > imports it in our local database, would there be some better way of
> > accomplishing this?
> >
> > is there some way i can constantly monitor what resources the data base is
> > using?
> >
> > any suggestions will be greatly appreciated,
> > Thx & Regards
> > Santu
> >
>
>

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any idea
what is taking up all of the memory?
Thanks, Sean
> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
http://www.aspfaq.com/
(Reverse address to reply.)

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any idea
what is taking up all of the memory?
Thanks, Sean> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
--
http://www.aspfaq.com/
(Reverse address to reply.)

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any ide
a
what is taking up all of the memory?
Thanks, Sean> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
http://www.aspfaq.com/
(Reverse address to reply.)

Performance problems with SQL commands in data flow task

SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:

UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '

Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.

Jeff-B wrote:

SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:

UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '

Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.

You should redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update. What you've got now is a new, distinct update command for every row on the update path. This is costly.|||

Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".

If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?

|||

Jeff-B wrote:

Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".

If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?

My suggestion of moving the data to a table was assuming you were doing a parameter-based update query.

Your understanding is correct. You were executing 84,000 updates, and generally there is never a good time to do that. If you need to perform an update in the data flow on all of those rows, it would be best to insert the changes into a separate table, to be used later in a set-based update.|||Thank you for your latest response and your help with this problem.|||

Jeff-B wrote:

Thank you for your latest response and your help with this problem.

Jeff,

According to your post you managed to achieve this with an Execute SQL Task. Am I correct?

If using an Execute SQL Task is an option for you then I would go with that over a data-flow every time. SSIS will almost never be able to perform quicker than a RDBMS engine.

-Jamie

|||

Jamie,

Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.

|||

Jeff-B wrote:

Jamie,

Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.

Caveat that with the fact that its efficient in certain circumstances - unfortunetely doing updates is one of those scenarios. That's due to the vary nature of updates.

-Jamie

sql

Performance Problem

Hi,
This is query that is causing problem
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
WHERE WO.AssignedTo = 1020
AND StateMaster.IsInDashboard = 1
1. WorkOrder table is master table which consists of 155986 rows.
2. Task table is the child table refering to workorder (Id) which is
having 516060 rows.
3. Statemaster is the master table consists of about 500 rows.
Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
the condition StateMaster.IsInDashboard = 1 the result set
will be minimized to 30 rows.
For executing the above query it is taking 1.7 sec.
This is the execution Plan i got when i run this query
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
| | |--Index
S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
| |--Clustered Index
S(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
SEEK:([StateMaster].[Id]=[WO].[StatusId]),
WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
|--Index
S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
I am not understanding why it is doing bookmark lookup on workorder
table when i joined StatusId column with Id column in statemaster table
and checking the condition Statemaster.IsinDashboard = 1.
These are the indexes we have on these tables.
1. In Task table "Id" is the primary Key and it is having
non-clustered index on workorderid
2. In Workorder table "Id" is the primary Key and it is having
non-clustered index on statusid
3. In Statemaster table "Id" is the primary Key
Could anyone help me out why the bookmark lookup is happening because
bookmark lookup on workorder table is taking above 95% of the total
query time.
Regards,
Ramnadh.
*** Sent via Developersdex http://www.examnotes.net ***I'd try creating an indexed view with the "StateMaster.IsInDashboard = 1"
condition on the dbo.StateMaster table.
I.e.:
create view <view name>
with schemabinding
as
select dbo.StateMaster.Id
,<column list>
from dbo.StateMaster
where (dbo.StateMaster.IsInDashboard = 1)
go
create unique clustered index <index name>
on <view name>
(
Id
)
go
Also consider using the READPAST hint instead of the notorious NOCOUNT hint.
Look up the details in Books Online.
ML
http://milambda.blogspot.com/|||> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
Does the StatusId column in WorkOrder table have an index?
"ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
news:e5p573KLGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is query that is causing problem
> SELECT T.Id AS TaskId
> FROM dbo.Task T (NOLOCK)
> INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
> INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
> WHERE WO.AssignedTo = 1020
> AND StateMaster.IsInDashboard = 1
> 1. WorkOrder table is master table which consists of 155986 rows.
> 2. Task table is the child table refering to workorder (Id) which is
> having 516060 rows.
> 3. Statemaster is the master table consists of about 500 rows.
> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
> the condition StateMaster.IsInDashboard = 1 the result set
> will be minimized to 30 rows.
> For executing the above query it is taking 1.7 sec.
>
> This is the execution Plan i got when i run this query
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
> OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
> | | |--Index
> S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
> SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
> | |--Clustered Index
> S(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
> SEEK:([StateMaster].[Id]=[WO].[StatusId]),
> WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
> |--Index
> S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
> SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
>
> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
> These are the indexes we have on these tables.
> 1. In Task table "Id" is the primary Key and it is having
> non-clustered index on workorderid
> 2. In Workorder table "Id" is the primary Key and it is having
> non-clustered index on statusid
> 3. In Statemaster table "Id" is the primary Key
> Could anyone help me out why the bookmark lookup is happening because
> bookmark lookup on workorder table is taking above 95% of the total
> query time.
> Regards,
> Ramnadh.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows
Since you have no index on AssigendTo, indexes can't be used to satisfy this
predicate and I suspect IsinDashboard isn't very selective. I suggest you
create a composite nonclustered index on AssigendTo and IsinDashboard.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFLTGNLLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Does the StatusId column in WorkOrder table have an index?
>
> "ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
> news:e5p573KLGHA.1424@.TK2MSFTNGP12.phx.gbl...
>|||Yep,Dan, I have missed that AssigendTo column has no index.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhRp1RLLGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Since you have no index on AssigendTo, indexes can't be used to satisfy
> this predicate and I suspect IsinDashboard isn't very selective. I
> suggest you create a composite nonclustered index on AssigendTo and
> IsinDashboard.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFLTGNLLGHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||I had defined a view by giving the query
create view taskview with schemabinding as
select dbo.StateMaster.Id
, IsTerminal
from dbo.StateMaster
where StateMaster.IsInDashboard = 1
and statemaster.isterminal = 0
and i had defined the clustered index on Id column of the view.it worked
fine and it was reduced to 400 ms.
1) How does the optimizer avoided the bookmark lookup by creating a view
and defining an index on the Id column .
2) i am having another problem when i added another table in the join
SELECT T.Id AS TaskId FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN taskview (NOLOCK) ON taskview.Id = WO.StatusId
inner join objectdetail OD(nolock) on wo.id = OD.objectid
WHERE WO.AssignedTo = 1020
and taskview.isterminal = 0
and OD.objecttypeid = 10
OR ( WO.AssignedTo = 1020 AND ISNULL(taskview.IsTerminal,0) = 0
AND( CONVERT(CHAR(8),'12/01/2005',112) <= CONVERT(CHAR(8),
DATEADD(DD,1,OD.LastStatusUpdDt) , 112) )
)
OR ( WO.AssignedTo = 1020 AND ISNULL(taskview.IsTerminal,0) = 1
AND( CONVERT(CHAR(8),'12/01/2005',112) <= CONVERT(CHAR(8) ,
OD.LastStatusUpdDt , 112) )
)
(ObjectTypeId,ObjectId) is the primary key on ObjectDetail table.It
consists of around 228313 rows. The condition Objecttypeid = 10 satifies
around 156000 rows.
It was taking above 3 sec for retrieving the 30 rows. The execution plan
is showing as below
|--Nested Loops(Inner Join, OUTER REFERENCES:([OD].[ObjectId]) WITH
PREFETCH)
|--Hash Match(Inner Join, HASH:([WO].[Id])=([OD].[ObjectId]),
RESIDUAL:(((Convert([taskview].[IsTerminal])=0 AND
[OD].[ObjectTypeId]=10) OR (Convert([taskview].[IsTerminal])=0 AND
'12/01/20'<=Convert(dateadd(day, 1, [OD].[LastStatusUpdDt])))) OR (Co
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([WO].[StatusId]))
| | |--Hash Match(Inner Join, HASH:([WO].[Id])=([WO].[Id]))
| | | |--Index
S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=1020) ORDERED FORWARD)
| | | |--Index
Scan(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder_StatusId] AS
[WO]))
| | |--Clustered Index
S(OBJECT:([Viper63].[dbo].[taskview].[taskid]),
SEEK:([taskview].[Id]=[WO].[StatusId]),
WHERE:(Convert([taskview].[IsTerminal])=0 OR
Convert([taskview].[IsTerminal])=1) ORDERED FORWARD)
| |--Clustered Index
Scan(OBJECT:([Viper63].[dbo].[ObjectDetail].[PK_ObjectDetail] AS [OD]),
WHERE:(([OD].[ObjectTypeId]=10 OR '12/01/2005'<=Convert(dateadd(day, 1,
[OD].[LastStatusUpdDt]))) OR
'12/01/2005'<=Convert([OD].[LastStatusUpdDt])))
|--Index
S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[OD].[ObjectId]) ORDERED FORWARD)
It was doing clustered Index scan on above 2 million rows of the
ObjectDetail table which is taking above 58% of the total query time.
I had defined Non-Clustered Index on "LastStatusUpdDt" column in the
Objectdetail table. It reduced to around 2 sec. It is showing the same
execution plan but now Clustered Index s on Task table and the first
hash match join are taking most of the query time. I am not getting why
the hashmatch join and clustered index s is taking much time in
executing the query.
Thanks a lot for the help.
*** Sent via Developersdex http://www.examnotes.net ***|||A few basic suggestions:
- where status columns are used (e.g. in your case "isTerminal",
"IsInDashboard") and values have low selectivity, consider adding indexed
views for each status value (as I've illustrated in a previous post). This
will improve performance of queries where a specific value for this status
column is used as a restrictive condition;
- avoid using functions in conditions (such as the ISNULL and CONVERT in
your where clause), especially if other methods can be used, since functions
cause scans, wile other methods enable use of indexes;
- avoid complex OR conditions - try using UNION - e.g.:
select ... from
where <one set of conditions>
union --all -- use all or not - depending on actual requirements (test,
test, test...)
select ... from
where <other set of conditions>
As Uri and Dan suggested - put an index on the AssignedTo column. A
nonclustered would do.
ML
http://milambda.blogspot.com/|||Thanks for the information. I left out the basic concepts of using
functions in the where clause which will affect the performance. thanks
for reminding me. i will check by removing the functions which will
enable using of indexes by engine and will check by replacing OR
condition with the union also in the query which will affect performance
badly.
Sorry Uri and Dan for not giving the complete information. there is a
non-clustered non-unique index on assignedto column on the workorder
table.
*** Sent via Developersdex http://www.examnotes.net ***|||I had changed the query by removing usage of functions.
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN taskview ON taskview.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE ( WO.AssignedTo = 1020 and ((taskview.IsTerminal = 1 AND
@.seldate <= OD.LastStatusUpdDt )
or (taskview.IsTerminal = 0 AND @.seldate <=
OD.LastStatusUpdDt+1 )
)
)
Replaced OR with the UNION using below query
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN TaskView ON TaskView.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = 1020
AND Taskview.IsTerminal = 0
AND @.seldate <= OD.LastStatusUpdDt + 1
UNION
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN TaskView ON TaskView.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = 1020
AND taskview.IsTerminal = 1
AND @.seldate <= OD.LastStatusUpdDt
i replaced the OR condition with UNION, but i am seeing same execution
plan using by optimizer.below is the execution plan generated
|--Nested Loops(Inner Join, OUTER REFERENCES:([OD].[ObjectId]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id],
[TASKVIEW].[isterminal]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([WO].[StatusId]))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Changeware].[dbo].[WorkOrder] AS [WO]))
| | | |--Index
S(OBJECT:([Changeware].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=1020) ORDERED FORWARD)
| | |--Clustered Index
S(OBJECT:([Changeware].[dbo].[TASKVIEW].[taskindex]),
SEEK:([TASKVIEW].[id]=[WO].[StatusId]),
WHERE:(Convert([TASKVIEW].[isterminal])=1 OR
Convert([TASKVIEW].[isterminal])=0) ORDERED FORWARD)
| |--Clustered Index
S(OBJECT:([Changeware].[dbo].[ObjectDetail].[PK_ObjectDetail] AS
[OD]), SEEK:([OD].[ObjectTypeId]=10 AND [OD].[ObjectId]=[WO].[Id]),
WHERE:(((Convert([TASKVIEW].[isterminal])=1 AND
[@.seldate]<=[OD].[LastStatusUpdDt]) OR (
|--Index
S(OBJECT:([Changeware].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[OD].[ObjectId]) ORDERED FORWARD)
It is doing bookmark lookup on the workorder table which is costing
around 92% of the total query cost. I am still not getting, with the
proper indexes on the statusid column and the assignedto column of the
workorder table why optimizer is doing bookmark lookup on workorder
table.
*** Sent via Developersdex http://www.examnotes.net ***|||have you tried indexes on WorkOrder(Id, AssignedTo) and
WorkOrder(AssignedTo, iD) ?

Performance Problem

Hi,
Below is the query that causing problems

SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
WHERE WO.AssignedTo = 1020
AND StateMaster.IsInDashboard = 1

1. WorkOrder table is master table which consists of 155986 rows.
2. Task table is the child table refering to workorder (Id) which is
having 516060 rows.
3. Statemaster is the master table consists of about 500 rows.

Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
the condition StateMaster.IsInDashboard = 1 the result set
will be minimized to 30 rows.

For executing the above query it is taking 1.7 sec.

This is the execution Plan i got when i run this query

|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
| | |--Index
Seek(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
| |--Clustered Index
Seek(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
SEEK:([StateMaster].[Id]=[WO].[StatusId]),

WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)

I am not understanding why it is doing bookmark lookup on workorder
table when i joined StatusId column with Id column in statemaster table
and checking the condition Statemaster.IsinDashboard = 1.

These are the indexes we have on these tables.

1. In Task table "Id" is the primary Key and it is having
non-clustered index on workorderid
2. In Workorder table "Id" is the primary Key and it is having
non-clustered index on statusid
3. In Statemaster table "Id" is the primary Key

Could anyone help me out why the bookmark lookup is happening, it is
taking about 95% of the query time.

Regards,
ramnadh.

*** Sent via Developersdex http://www.developersdex.com ***This question has been answered in microsoft.public.sqlserver.programming.
Please don't post the same question independently to multiple groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
news:hLlGf.1$gp2.651@.news.uswest.net...
> Hi,
> Below is the query that causing problems
> SELECT T.Id AS TaskId
> FROM dbo.Task T (NOLOCK)
> INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
> INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
> WHERE WO.AssignedTo = 1020
> AND StateMaster.IsInDashboard = 1
> 1. WorkOrder table is master table which consists of 155986 rows.
> 2. Task table is the child table refering to workorder (Id) which is
> having 516060 rows.
> 3. Statemaster is the master table consists of about 500 rows.
> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
> the condition StateMaster.IsInDashboard = 1 the result set
> will be minimized to 30 rows.
> For executing the above query it is taking 1.7 sec.
>
> This is the execution Plan i got when i run this query
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
> OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
> | | |--Index
> Seek(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
> SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
> | |--Clustered Index
> Seek(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
> SEEK:([StateMaster].[Id]=[WO].[StatusId]),
> WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
> |--Index
> Seek(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
> SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
>
> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
> These are the indexes we have on these tables.
> 1. In Task table "Id" is the primary Key and it is having
> non-clustered index on workorderid
> 2. In Workorder table "Id" is the primary Key and it is having
> non-clustered index on statusid
> 3. In Statemaster table "Id" is the primary Key
> Could anyone help me out why the bookmark lookup is happening, it is
> taking about 95% of the query time.
> Regards,
> ramnadh.
> *** Sent via Developersdex http://www.developersdex.com ***