|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is it possible to trace all statements to a specific table?...Hello, all. Using SQL 2005. My developers feel their app is slow due to
either non-indexed columns that should be indexed, or improperly indexed columns. What I'm wanting to do is trace the workload against this table for a few hours, and use this workload to determine what columns can benefit from indexing...using the Index Tuning Wizard. My question: is it possible to trace any INSERT, UPDATE, DELETE, SELECT statements on a specific table in Profiler? If so, what template would be a good baseline to use and how would I setup my filtering? Much, much thanx! Roz I would use profiler with the TSQL or the TSQL duration template, save it to
a database (for analysis) or a file (for rerunning against dev db). If these statements are embedded in to Stored Procs then I would use the TSQL_SPs Template. see if that helps.. thanks, Show quote "Roz" <R**@discussions.microsoft.com> wrote in message news:7828C95E-43A0-46F7-8BA5-AB7775A56FE7@microsoft.com... > Hello, all. Using SQL 2005. My developers feel their app is slow due to > either non-indexed columns that should be indexed, or improperly indexed > columns. What I'm wanting to do is trace the workload against this table > for a few hours, and use this workload to determine what columns can > benefit > from indexing...using the Index Tuning Wizard. My question: is it > possible > to trace any INSERT, UPDATE, DELETE, SELECT statements on a specific table > in > Profiler? If so, what template would be a good baseline to use and how > would > I setup my filtering? > > Much, much thanx! > > Roz Warren,
Thanks for the reply. Is there a way to only look for a specific table? Roz Show quote "Warren Brunk" wrote: > I would use profiler with the TSQL or the TSQL duration template, save it to > a database (for analysis) or a file (for rerunning against dev db). > If these statements are embedded in to Stored Procs then I would use the > TSQL_SPs Template. > > see if that helps.. > > thanks, > > -- > /* > Warren Brunk - MCITP - SQL 2005, MCDBA > www.techintsolutions.com > */ > > > "Roz" <R**@discussions.microsoft.com> wrote in message > news:7828C95E-43A0-46F7-8BA5-AB7775A56FE7@microsoft.com... > > Hello, all. Using SQL 2005. My developers feel their app is slow due to > > either non-indexed columns that should be indexed, or improperly indexed > > columns. What I'm wanting to do is trace the workload against this table > > for a few hours, and use this workload to determine what columns can > > benefit > > from indexing...using the Index Tuning Wizard. My question: is it > > possible > > to trace any INSERT, UPDATE, DELETE, SELECT statements on a specific table > > in > > Profiler? If so, what template would be a good baseline to use and how > > would > > I setup my filtering? > > > > Much, much thanx! > > > > Roz > > > I would use the SQLProfilerTuning template, and on the [Filters] tab, you
can select an [ObjectName] and input your table names, one per line. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "Roz" <R**@discussions.microsoft.com> wrote in message news:7828C95E-43A0-46F7-8BA5-AB7775A56FE7@microsoft.com... > Hello, all. Using SQL 2005. My developers feel their app is slow due to > either non-indexed columns that should be indexed, or improperly indexed > columns. What I'm wanting to do is trace the workload against this table > for a few hours, and use this workload to determine what columns can > benefit > from indexing...using the Index Tuning Wizard. My question: is it > possible > to trace any INSERT, UPDATE, DELETE, SELECT statements on a specific table > in > Profiler? If so, what template would be a good baseline to use and how > would > I setup my filtering? > > Much, much thanx! > > Roz Thanks Arnie. I'll give it a shot.
Roz Show quote "Arnie Rowland" wrote: > I would use the SQLProfilerTuning template, and on the [Filters] tab, you > can select an [ObjectName] and input your table names, one per line. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Roz" <R**@discussions.microsoft.com> wrote in message > news:7828C95E-43A0-46F7-8BA5-AB7775A56FE7@microsoft.com... > > Hello, all. Using SQL 2005. My developers feel their app is slow due to > > either non-indexed columns that should be indexed, or improperly indexed > > columns. What I'm wanting to do is trace the workload against this table > > for a few hours, and use this workload to determine what columns can > > benefit > > from indexing...using the Index Tuning Wizard. My question: is it > > possible > > to trace any INSERT, UPDATE, DELETE, SELECT statements on a specific table > > in > > Profiler? If so, what template would be a good baseline to use and how > > would > > I setup my filtering? > > > > Much, much thanx! > > > > Roz > > > Roz
In SQL Server 2005 for INSERT/DELETE/UPDATE you may want to use an OUTPUT clause to capture the things create table test ( i int identity not null primary key, j int not null unique ) create table #new ( i int not null, j int not null) insert into test (j) output inserted.i, inserted.j into #new select o.object_id from sys.objects as o select * from #new go Actually the problem comes with SELECT statement , so SQL Server Profiler is your friend here , Also , serach on internet for 'SELECT+Triggers' some posts of Dejan Sarka may give you an idea Show quote "Roz" <R**@discussions.microsoft.com> wrote in message news:7828C95E-43A0-46F7-8BA5-AB7775A56FE7@microsoft.com... > Hello, all. Using SQL 2005. My developers feel their app is slow due to > either non-indexed columns that should be indexed, or improperly indexed > columns. What I'm wanting to do is trace the workload against this table > for a few hours, and use this workload to determine what columns can > benefit > from indexing...using the Index Tuning Wizard. My question: is it > possible > to trace any INSERT, UPDATE, DELETE, SELECT statements on a specific table > in > Profiler? If so, what template would be a good baseline to use and how > would > I setup my filtering? > > Much, much thanx! > > Roz  |
|||||||||||||||||||||||
Other interesting topics