Capture table & index scanning

I have a table I believe is the subject of excessive
scanning on the table & its indexes. I am having trouble
getting SQLProfiler to capture the calls to SPs that are
causing scans on the table. Anybody know how to config
SQLProfiler to do this or a better way?
Thanks,
Roger
Have you set filters to search for the SP name?
TextData--> Like %whatever%
I use this combined with the databaseid filter to limit the thousands of
rows my dbs put out to Profiler
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger
|||Roger
http://www.sql-server-performance.co...filer_tips.asp
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
> I have a table I believe is the subject of excessive
> scanning on the table & its indexes. I am having trouble
> getting SQLProfiler to capture the calls to SPs that are
> causing scans on the table. Anybody know how to config
> SQLProfiler to do this or a better way?
> Thanks,
> Roger
|||Thanks for the reply Kevin. I know the table & its
actually the SPs I'm trying to identify. Here's my
Profiler settings:
Events:
Scan:Started
RPC:Completed
SP:Completed
Data Columns:
IndexID (Grouped)
EventClass
StartTime
SPID
Duration
TextData
HostName
DatabaseID
ApplicationName
CPU
Reads
Filters:
DatabaseID = 8
ObjectID = 295776211 (the table thats being scanned)
I suspect the table is being scanned because SQL slows
down, CPU goes up & I run sp_lock & a few SPIDs have
taken out 2million+ locks on the table. The above
Profiler settings indicate scans, but doesn't tell me
what SPs are running when the scan occurs. Any help
appreciated.
Thanks,
Roger

>--Original Message--
>Have you set filters to search for the SP name?
>TextData--> Like %whatever%
>I use this combined with the databaseid filter to limit
the thousands of
>rows my dbs put out to Profiler
>--
>Kevin Hill
>President
>3NF Consulting
>www.3nf-inc.com/NewsGroups.htm
>www.DallasDBAs.com/forum - new DB forum for Dallas/Ft.
Worth area DBAs.
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:1f72301c457be$1c6e9b30$a401280a@.phx.gbl...
trouble[vbcol=seagreen]
are
>
>.
>
|||Thanks Uri. I had already found this article. The whole
site is excellent. Please see my reply to Kevin. I
would appreciate any more help as this is really hurting
our production system.
Thanks,
Roger
>--Original Message--
>Roger
>http://www.sql-server-
performance.com/sql_server_profiler_tips.asp
>
|||Roger
Can you identify where does the table involve?
Look at these stored procedure/queries.
Have you appropriate indexes for searching on the table?
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks Uri. I had already found this article. The whole
> site is excellent. Please see my reply to Kevin. I
> would appreciate any more help as this is really hurting
> our production system.
> Thanks,
> Roger
> performance.com/sql_server_profiler_tips.asp
|||Uri,
We have 4000 SPs. The table is probably called by
hundreds of these SPs. Through knowledge of the front-
ends, we have clustered, non-clustered & composite
indexes which we have seen improvements in some SPs, but
we are still experiencing problems. Can I assume that
SQLProfiler can't do this?
Thanks,
Roger

>--Original Message--
>Roger
>Can you identify where does the table involve?
>Look at these stored procedure/queries.
>Have you appropriate indexes for searching on the table?
>
>
>"Roger White" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
whole[vbcol=seagreen]
hurting
>
>.
>
|||Can you set a filter on the duration to only show those that are taking over
a certain amount of time?
I have a trace template established to identify anything over 5000 (5
seconds) in my environment...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fddf01c45835$fc33ca20$a001280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the reply Kevin. I know the table & its
> actually the SPs I'm trying to identify. Here's my
> Profiler settings:
> Events:
> Scan:Started
> RPC:Completed
> SP:Completed
> Data Columns:
> IndexID (Grouped)
> EventClass
> StartTime
> SPID
> Duration
> TextData
> HostName
> DatabaseID
> ApplicationName
> CPU
> Reads
> Filters:
> DatabaseID = 8
> ObjectID = 295776211 (the table thats being scanned)
> I suspect the table is being scanned because SQL slows
> down, CPU goes up & I run sp_lock & a few SPIDs have
> taken out 2million+ locks on the table. The above
> Profiler settings indicate scans, but doesn't tell me
> what SPs are running when the scan occurs. Any help
> appreciated.
> Thanks,
> Roger
> the thousands of
> Worth area DBAs.
> wrote in message
> trouble
> are
|||Roger
Well, I would go with idetifying those stored procedures (although it might
be >100) and then run SQL Server Profiler to capture a long running stored
procedures (group by duration).
"Roger White" <anonymous@.discussions.microsoft.com> wrote in message
news:1fa4401c4584c$6739b350$a101280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> We have 4000 SPs. The table is probably called by
> hundreds of these SPs. Through knowledge of the front-
> ends, we have clustered, non-clustered & composite
> indexes which we have seen improvements in some SPs, but
> we are still experiencing problems. Can I assume that
> SQLProfiler can't do this?
> Thanks,
> Roger
> wrote in message
> whole
> hurting