kill running query: profiler

Showing posts with label profiler. Show all posts

Monday, March 12, 2012

how to list all tables used in a database?

Hi,
I want to list all the tables used in a particular database during the
execution of a lot of DTS packages.
I think the profiler could help me, but what is the configuration to
accomplish this?
event, columns, filters...
thanks for your guide
Jerome.Hi
The below script find an emtpy tables , modify it for your needs
use pubs
-- create table and do not populate it
create table EmptyTable (c1 int)
go
-- create a cursor to go through each table in a database
-- and print the name of any empty tables
DECLARE @.SQLString nvarchar (255),
@.ParmDefinition nvarchar (255)
DECLARE @.tablename sysname, @.Empty char (1)
DECLARE FindEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN FindEmptyTables
FETCH NEXT FROM FindEmptyTables INTO @.tablename
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
@.Empty = ''N'' ELSE set @.Empty = ''Y'''
SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.tablename = @.tablename,
@.Empty = @.Empty OUTPUT
IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
FETCH NEXT FROM FindEmptyTables INTO @.tablename
END
CLOSE FindEmptyTables
DEALLOCATE FindEmptyTables
GO
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> Hi,
> I want to list all the tables used in a particular database during the
> execution of a lot of DTS packages.
> I think the profiler could help me, but what is the configuration to
> accomplish this?
> event, columns, filters...
> thanks for your guide
> Jerome.
>|||I don't want to list tables availables, I want to list table REALLY used
during the loading process.
I mean tables which is part of a Select statement.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Hi
> The below script find an emtpy tables , modify it for your needs
> use pubs
> -- create table and do not populate it
> create table EmptyTable (c1 int)
> go
> -- create a cursor to go through each table in a database
> -- and print the name of any empty tables
> DECLARE @.SQLString nvarchar (255),
> @.ParmDefinition nvarchar (255)
> DECLARE @.tablename sysname, @.Empty char (1)
> DECLARE FindEmptyTables CURSOR READ_ONLY
> FOR SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> OPEN FindEmptyTables
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> WHILE (@.@.fetch_status = 0)
> BEGIN
> SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> EXECUTE sp_executesql
> @.SQLString,
> @.ParmDefinition,
> @.tablename = @.tablename,
> @.Empty = @.Empty OUTPUT
> IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> END
> CLOSE FindEmptyTables
> DEALLOCATE FindEmptyTables
> GO
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>> Hi,
>> I want to list all the tables used in a particular database during the
>> execution of a lot of DTS packages.
>> I think the profiler could help me, but what is the configuration to
>> accomplish this?
>> event, columns, filters...
>> thanks for your guide
>> Jerome.
>>
>|||Ok, run SQL Server Profiler to identify those tables , save the data into a
table in SQL Server and make a query to get report.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> I don't want to list tables availables, I want to list table REALLY used
> during the loading process.
> I mean tables which is part of a Select statement.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> > Hi
> > The below script find an emtpy tables , modify it for your needs
> > use pubs
> >
> > -- create table and do not populate it
> > create table EmptyTable (c1 int)
> > go
> >
> > -- create a cursor to go through each table in a database
> > -- and print the name of any empty tables
> > DECLARE @.SQLString nvarchar (255),
> > @.ParmDefinition nvarchar (255)
> >
> > DECLARE @.tablename sysname, @.Empty char (1)
> >
> > DECLARE FindEmptyTables CURSOR READ_ONLY
> > FOR SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > OPEN FindEmptyTables
> >
> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
> > WHILE (@.@.fetch_status = 0)
> > BEGIN
> > SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> > @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> > SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> >
> > EXECUTE sp_executesql
> > @.SQLString,
> > @.ParmDefinition,
> > @.tablename = @.tablename,
> > @.Empty = @.Empty OUTPUT
> >
> > IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
> > END
> >
> > CLOSE FindEmptyTables
> > DEALLOCATE FindEmptyTables
> > GO
> >
> > "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> > news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> >> Hi,
> >>
> >> I want to list all the tables used in a particular database during the
> >> execution of a lot of DTS packages.
> >>
> >> I think the profiler could help me, but what is the configuration to
> >> accomplish this?
> >> event, columns, filters...
> >>
> >> thanks for your guide
> >> Jerome.
> >>
> >>
> >
> >
>|||yes, I know...
but what filters, events and columns I have to select to accomplish this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUm3IlxSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Ok, run SQL Server Profiler to identify those tables , save the data into
> a
> table in SQL Server and make a query to get report.
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> I don't want to list tables availables, I want to list table REALLY used
>> during the loading process.
>> I mean tables which is part of a Select statement.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
>> > Hi
>> > The below script find an emtpy tables , modify it for your needs
>> > use pubs
>> >
>> > -- create table and do not populate it
>> > create table EmptyTable (c1 int)
>> > go
>> >
>> > -- create a cursor to go through each table in a database
>> > -- and print the name of any empty tables
>> > DECLARE @.SQLString nvarchar (255),
>> > @.ParmDefinition nvarchar (255)
>> >
>> > DECLARE @.tablename sysname, @.Empty char (1)
>> >
>> > DECLARE FindEmptyTables CURSOR READ_ONLY
>> > FOR SELECT TABLE_NAME
>> > FROM INFORMATION_SCHEMA.TABLES
>> > WHERE TABLE_TYPE = 'BASE TABLE'
>> >
>> > OPEN FindEmptyTables
>> >
>> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
>> > WHILE (@.@.fetch_status = 0)
>> > BEGIN
>> > SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
>> > @.Empty = ''N'' ELSE set @.Empty = ''Y'''
>> > SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
>> >
>> > EXECUTE sp_executesql
>> > @.SQLString,
>> > @.ParmDefinition,
>> > @.tablename = @.tablename,
>> > @.Empty = @.Empty OUTPUT
>> >
>> > IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
>> > FETCH NEXT FROM FindEmptyTables INTO @.tablename
>> > END
>> >
>> > CLOSE FindEmptyTables
>> > DEALLOCATE FindEmptyTables
>> > GO
>> >
>> > "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
>> > news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>> >> Hi,
>> >>
>> >> I want to list all the tables used in a particular database during the
>> >> execution of a lot of DTS packages.
>> >>
>> >> I think the profiler could help me, but what is the configuration to
>> >> accomplish this?
>> >> event, columns, filters...
>> >>
>> >> thanks for your guide
>> >> Jerome.
>> >>
>> >>
>> >
>> >
>>
>

Wednesday, March 7, 2012

How to know which transaction used more than 1 CPU ?

I am using the SQL profiler to monitor my server. I am
trying to search for those queries who seem to put bussy
all the 8 cpu's that my server has.
I can see a CPU column which has some values, but how can
I know if that particular statement used more than one
CPU ?
Thanks in advance,Profiler will not give you that information. In fact I don't really think it
would be of much use. SQL Server may spawn extra threads to complete a
query, depending on the parallelism the optimizer uses and those threads
will show up in Profiler as new "connections' to the server.
Jim
<craigkenisston@.hotmail.com> wrote in message
news:0c3a01c3d573$bc2ee0d0$a001280a@.phx.gbl...
> I am using the SQL profiler to monitor my server. I am
> trying to search for those queries who seem to put bussy
> all the 8 cpu's that my server has.
> I can see a CPU column which has some values, but how can
> I know if that particular statement used more than one
> CPU ?
> Thanks in advance,
>|||| I am using the SQL profiler to monitor my server. I am
| trying to search for those queries who seem to put bussy
| all the 8 cpu's that my server has.
|
| I can see a CPU column which has some values, but how can
| I know if that particular statement used more than one
| CPU ?
--
Hi Craig,
From SQL profiler, You can monitor parallelism using the Execution Plan
event under the Performance category. You can inspect the execution tree
and view if parallelism was used in the query.
Hope this helps,
--
Eric Cárdenas
SQL Server support

Monday, March 12, 2012

how to list all tables used in a database?

how to list all tables used in a database?

how to list all tables used in a database?

Wednesday, March 7, 2012

How to know which transaction used more than 1 CPU ?

kill running query

Blog Archive

About Me