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,
RogerHave 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.com/sql_server_profiler_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
>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 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...
> 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
> >|||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
>news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
>> 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
>> >
>
>.
>|||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...
> 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
> >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
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...
> 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
> >news:1fa4c01c45836$aad376c0$a501280a@.phx.gbl...
> >> 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
> >> >
> >
> >
> >.
> >