Showing posts with label total. Show all posts
Showing posts with label total. Show all posts

Wednesday, March 21, 2012

Query Total Response Time

How can I know the total time (in miliseconds) for my SQL Server to answer a
query. I want to issue a query using an Query Analyzer and Measure the
Servers total response time. Thanks in advance!
On Wed, 2 Mar 2005 23:51:02 -0800, Borikoy wrote:

>How can I know the total time (in miliseconds) for my SQL Server to answer a
>query. I want to issue a query using an Query Analyzer and Measure the
>Servers total response time. Thanks in advance!
Hi Borikoy,
The simplest form is
SELECT CURRENT_TIMESTAMP
-- Insert your query here
SELECT CURRENT_TIMESTAMP
Slightly more advanced would be
DECLARE @.start datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert your query here
SET @.end = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.start, @.end)
In both cases, the time used to push the rows to the client is included.
For a clean measurement of time taken to process the query, I usually
change all SELECT queries to SELECT INTO queries (so that the ooutput
goes in a temp table instead of over a possibly slow network, or is
slowed by display processing).
Another way to get information about time taken is:
SET STATISTICS TIME ON
-- Insert your query here
SET STATISTICS TIME OFF
Check out the description of SET STATISTICS TIME in Books Online. And
you might wish to read up on SET STATISTICS IO as well.
Whatever way you use, you won't get accurate comparisons if you run
queries with data in the cache. Often, the first execution of a query
takes longer because data needs to be brought into cache from hard disk;
subsequent invocations go lots faster because the data is still in
cache, so the slow hard disk access is not necessary anymore. To clear
out the data cache and the procedure cache as well (so you won't reuse
old execution plans), run the following statements before doing your
tests:
CHECKPOINT -- Write dirty pages to disk
DBCC DROPCLEANBUFFERS -- Flush all non-dirty pages from cache
DBCC FREEPROCCACHE -- Remove all cached execution plans
Running the above statements on a production database will severely
impact the performance of your running processes - but I assume that you
don't intend to run performance tests on a live production database
anyway!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

Query total number of records in a schema

Please help out the neophyte!

I'm trying to query a database for the total number of records in each table owned by a particular schema in SQL Server 2000. I can query INFORMATION_SCHEMA.TABLES for a list of the tables in a particular schema, but how do I pass these table names to variable that can be used to count(*) the records in each? Can anyone show me how this should be done using simple TSQL?

Thanks.

Chris

Give this a shot:

Code Snippet

DECLARE @.TableName varchar(100)

DECLARE @.SQL nvarchar(MAX)

DECLARE SchemaCounter CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN SchemaCounter

FETCH NEXT FROM SchemaCounter INTO @.TableName

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.SQL = 'SELECT COUNT(*) [' + @.TableName + ' COUNT] FROM [' + @.TableName + ']'

EXEC sp_executesql @.SQL

FETCH NEXT FROM SchemaCounter INTO @.TableName

END

CLOSE SchemaCounter

DEALLOCATE SchemaCounter

|||There is a couple of ways to get it through T-SQL, but I would not call them "simple" in neither case:

Code Snippet

select o.name, i.rowcnt
from sysindexes i
inner join sysobjects o on o.id = i.id
where o.type = 'U'
and i.indid in (0,1)


Or this one (still works in MSSQL2005):

Code Snippet

exec sp_MStablespace 'MyTableName'


The only drawback of these methods is that they used undocumented or deprecated features of SQL Server. Only hope you know what the word "undocumented" really means in MSSQL.


PS There is also a way to use sp_MSForEachTable system stored proc, but it's also undocumented Smile.|||

Hey...

rowcnt in sysindexes

It is very dangerous to use. One can not assure it gives exact number of rows and I am not sure if we can use this or not?

Regards,

Subbu

|||

cannot trust rowcnt in sysindexes. what do u say ?

|||

Yes.. Here the stright forward query (without undocumented sps)

Code Snippet

Select

OBJ.NAME [TableName],

IND.RowCnt as [RowCount]

From

dbo.sysindexes IND

Join sysobjects OBJ on IND.ID = OBJ.ID and indid < 2 And OBJ.Type='U'

order By 1

|||

Hey, the neophyte thanks you for your help. A nice simple solution to my problem.

Chris

|||RowCnt is not updated in "real time". It not ever guaranteed to be 100% accurate. However, if you only want a very reliable "ballpark" number, it works really well. I use it all the time with the caviat that the number is a guidline, rather than "in-stone."

Tuesday, March 20, 2012

Query to list disk drives and capacity

I need a query to list all my disk drives, total disk capacity and free
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank you
Didn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools such
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:

> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>

Query to list disk drives and capacity

I need a query to list all my disk drives, total disk capacity and free
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank youDidn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools such
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:
> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>

Query to list disk drives and capacity

I need a query to list all my disk drives, total disk capacity and free
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank youDidn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools suc
h
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:

> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>

Monday, March 12, 2012

Query to give me list of dbs + db size

How can I get a query that i can run against a SQL Server to get a list of
dbs and its size such as
Name Total DB Size Allocated DB Usage Total Log Size
Allocated Log Usage
DB1 120GB 100GB 20GB
1GB
DB2 1GB .7GB 50MB
25MB
master 100MB 50MB 100MB
15MB
...etc..
I would like to get a report in the same format as above.
Thanks
P.S. Using SQL 2000
Hassan,
Try chewing on this for a while.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
declare
@.dbName sysname,
@.sqlStr varchar(8000)
declare
@.DataFileP dec(15), @.DataFileB dec(19),
@.LogFileP dec(15), @.LogFileB dec(19),
@.ReservedP dec(15), @.ReservedB dec(19),
@.UserHeapP dec(15), @.UserHeapB dec(19),
@.SysHeapP dec(15), @.SysHeapB dec(19),
@.TableClusterP dec(15), @.TableClusterB dec(19),
@.ViewClusterP dec(15), @.ViewClusterB dec(19),
@.BlobP dec(15), @.BlobB dec(19),
@.IndexP dec(15), @.IndexB dec(19)
declare
@.bpp smallint -- bytes per page
select @.bpp = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select
'dbName',
'DataFileB',
'LogFileB',
'ReservedB',
'UserHeapB',
'SysHeapB',
'TableClusterB',
'ViewClusterB',
'BlobB',
'IndexB',
'DataB',
'FreeB'
create table #Sizes (
[dbName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataFileB] [decimal](19, 0) NULL ,
[LogFileB] [decimal](19, 0) NULL ,
[ReservedB] [decimal](19, 0) NULL ,
[UserHeapB] [decimal](19, 0) NULL ,
[SysHeapB] [decimal](19, 0) NULL ,
[TableClusterB] [decimal](19, 0) NULL ,
[ViewClusterB] [decimal](19, 0) NULL ,
[BlobB] [decimal](19, 0) NULL ,
[IndexB] [decimal](19, 0) NULL ,
[DataB] [decimal](22, 0) NULL ,
[FreeB] [decimal](24, 0) NULL
)
--^v^--
--^v^-- Start CursorProcessingDescription
--^v^--
declare Databases cursor
static forward_only
for
select name
from master.dbo.sysdatabases
order by name
open Databases
fetch next from Databases
into @.dbName
while (@.@.FETCH_STATUS = 0) begin
set @.sqlStr = REPLACE('
create view sysf_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysfiles
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view sysi_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysindexes
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view syso_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysobjects
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
-- Get Data File Space
select @.DataFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 = 0)
set @.DataFileB = @.DataFileP * @.bpp
-- Get Log File Space
select @.LogFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 != 0)
set @.LogFileB = @.LogFileP * @.bpp
-- Get Reserved Space
select @.ReservedP = ISNULL(SUM(CONVERT(dec(15),reserved)), 0)
from sysi_spaceused
where indid in (0, 1, 255)
set @.ReservedB = @.ReservedP * @.bpp
-- Get User Heap Space
select @.UserHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id in (select id from syso_spaceused where type = 'u')
set @.UserHeapB = @.UserHeapP * @.bpp
-- Get System Heap Space
select @.SysHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id not in (select id from syso_spaceused where type = 'u')
set @.SysHeapB = @.SysHeapP * @.bpp
-- Get Table Clustered Index Space
select @.TableClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id not in (select id from syso_spaceused where type = 'v')
set @.TableClusterB = @.TableClusterP * @.bpp
-- Get Indexed View Clustered Index Space
select @.ViewClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id in (select id from syso_spaceused where type = 'v')
set @.ViewClusterB = @.ViewClusterP * @.bpp
-- Get Blob Space
select @.BlobP = ISNULL(SUM(CONVERT(dec(15),used)), 0)
from sysi_spaceused
where indid = 255
set @.BlobB = @.BlobP * @.bpp
-- Get Index Space
select @.IndexP = SUM(CONVERT(dec(15),used)) - @.UserHeapP - @.SysHeapP -
@.TableClusterP - @.ViewClusterP - @.BlobP
from sysi_spaceused
where indid in (0, 1, 255)
set @.IndexB = @.IndexP * @.bpp
-- Report Results
-- select
-- @.dbName as dbName,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.DataFileB), 1), '.00',
'') as DataFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.LogFileB), 1), '.00',
'') as LogFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB), 1), '.00',
'') as ReservedB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB), 1), '.00', '')
as HeapB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.TableClusterB), 1),
'.00', '') as TableClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ViewClusterB), 1),
'.00', '') as ViewClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.BlobB), 1), '.00', '')
as BlobB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.IndexB), 1), '.00', '')
as IndexB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB + @.TableClusterB +
@.ViewClusterB + @.BlobB), 1), '.00', '') as DataB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB - (@.HeapB +
@.TableClusterB + @.ViewClusterB + @.BlobB + @.IndexB)), 1), '.00', '') as FreeB
insert #Sizes
select
@.dbName as dbName,
@.DataFileB as DataFileB,
@.LogFileB as LogFileB,
@.ReservedB as ReservedB,
@.UserHeapB as UserHeapB,
@.SysHeapB as SysHeapB,
@.TableClusterB as TableClusterB,
@.ViewClusterB as ViewClusterB,
@.BlobB as BlobB,
@.IndexB as IndexB,
@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB + @.BlobB as
DataB,
@.ReservedB - (@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB +
@.BlobB + @.IndexB) as FreeB
drop view sysf_spaceused
drop view sysi_spaceused
drop view syso_spaceused
--^v^-- End CursorProcessingDescription
fetch next from Databases
into @.dbName
end
close Databases
deallocate Databases
--^v^-- End CursorProcessingDescription
select * from #Sizes
drop table #Sizes
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
"Hassan" <hassan@.hotmail.com> wrote in message
news:u11AVVbYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> How can I get a query that i can run against a SQL Server to get a list of
> dbs and its size such as
> Name Total DB Size Allocated DB Usage Total Log Size
> Allocated Log Usage
> DB1 120GB 100GB 20GB 1GB
> DB2 1GB .7GB 50MB
> 25MB
> master 100MB 50MB 100MB 15MB
> ...etc..
> I would like to get a report in the same format as above.
> Thanks
> P.S. Using SQL 2000
>

Query to give me list of dbs + db size

How can I get a query that i can run against a SQL Server to get a list of
dbs and its size such as
Name Total DB Size Allocated DB Usage Total Log Size
Allocated Log Usage
DB1 120GB 100GB 20GB
1GB
DB2 1GB .7GB 50MB
25MB
master 100MB 50MB 100MB
15MB
...etc..
I would like to get a report in the same format as above.
Thanks
P.S. Using SQL 2000Hassan,
Try chewing on this for a while.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
declare
@.dbName sysname,
@.sqlStr varchar(8000)
declare
@.DataFileP dec(15), @.DataFileB dec(19),
@.LogFileP dec(15), @.LogFileB dec(19),
@.ReservedP dec(15), @.ReservedB dec(19),
@.UserHeapP dec(15), @.UserHeapB dec(19),
@.SysHeapP dec(15), @.SysHeapB dec(19),
@.TableClusterP dec(15), @.TableClusterB dec(19),
@.ViewClusterP dec(15), @.ViewClusterB dec(19),
@.BlobP dec(15), @.BlobB dec(19),
@.IndexP dec(15), @.IndexB dec(19)
declare
@.bpp smallint -- bytes per page
select @.bpp = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select
'dbName',
'DataFileB',
'LogFileB',
'ReservedB',
'UserHeapB',
'SysHeapB',
'TableClusterB',
'ViewClusterB',
'BlobB',
'IndexB',
'DataB',
'FreeB'
create table #Sizes (
[dbName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DataFileB] [decimal](19, 0) NULL ,
[LogFileB] [decimal](19, 0) NULL ,
[ReservedB] [decimal](19, 0) NULL ,
[UserHeapB] [decimal](19, 0) NULL ,
[SysHeapB] [decimal](19, 0) NULL ,
[TableClusterB] [decimal](19, 0) NULL ,
[ViewClusterB] [decimal](19, 0) NULL ,
[BlobB] [decimal](19, 0) NULL ,
[IndexB] [decimal](19, 0) NULL ,
[DataB] [decimal](22, 0) NULL ,
[FreeB] [decimal](24, 0) NULL
)
--^v^--
--^v^-- Start CursorProcessingDescription
--^v^--
declare Databases cursor
static forward_only
for
select name
from master.dbo.sysdatabases
order by name
open Databases
fetch next from Databases
into @.dbName
while (@.@.FETCH_STATUS = 0) begin
set @.sqlStr = REPLACE('
create view sysf_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysfiles
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view sysi_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysindexes
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view syso_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysobjects
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
-- Get Data File Space
select @.DataFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 = 0)
set @.DataFileB = @.DataFileP * @.bpp
-- Get Log File Space
select @.LogFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 != 0)
set @.LogFileB = @.LogFileP * @.bpp
-- Get Reserved Space
select @.ReservedP = ISNULL(SUM(CONVERT(dec(15),reserved)), 0)
from sysi_spaceused
where indid in (0, 1, 255)
set @.ReservedB = @.ReservedP * @.bpp
-- Get User Heap Space
select @.UserHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id in (select id from syso_spaceused where type = 'u')
set @.UserHeapB = @.UserHeapP * @.bpp
-- Get System Heap Space
select @.SysHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id not in (select id from syso_spaceused where type = 'u')
set @.SysHeapB = @.SysHeapP * @.bpp
-- Get Table Clustered Index Space
select @.TableClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id not in (select id from syso_spaceused where type = 'v')
set @.TableClusterB = @.TableClusterP * @.bpp
-- Get Indexed View Clustered Index Space
select @.ViewClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id in (select id from syso_spaceused where type = 'v')
set @.ViewClusterB = @.ViewClusterP * @.bpp
-- Get Blob Space
select @.BlobP = ISNULL(SUM(CONVERT(dec(15),used)), 0)
from sysi_spaceused
where indid = 255
set @.BlobB = @.BlobP * @.bpp
-- Get Index Space
select @.IndexP = SUM(CONVERT(dec(15),used)) - @.UserHeapP - @.SysHeapP -
@.TableClusterP - @.ViewClusterP - @.BlobP
from sysi_spaceused
where indid in (0, 1, 255)
set @.IndexB = @.IndexP * @.bpp
-- Report Results
-- select
-- @.dbName as dbName,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.DataFileB), 1), '.00',
'') as DataFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.LogFileB), 1), '.00',
'') as LogFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB), 1), '.00',
'') as ReservedB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB), 1), '.00', '')
as HeapB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.TableClusterB), 1),
'.00', '') as TableClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ViewClusterB), 1),
'.00', '') as ViewClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.BlobB), 1), '.00', '')
as BlobB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.IndexB), 1), '.00', '')
as IndexB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB + @.TableClusterB +
@.ViewClusterB + @.BlobB), 1), '.00', '') as DataB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB - (@.HeapB +
@.TableClusterB + @.ViewClusterB + @.BlobB + @.IndexB)), 1), '.00', '') as FreeB
insert #Sizes
select
@.dbName as dbName,
@.DataFileB as DataFileB,
@.LogFileB as LogFileB,
@.ReservedB as ReservedB,
@.UserHeapB as UserHeapB,
@.SysHeapB as SysHeapB,
@.TableClusterB as TableClusterB,
@.ViewClusterB as ViewClusterB,
@.BlobB as BlobB,
@.IndexB as IndexB,
@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB + @.BlobB as
DataB,
@.ReservedB - (@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB +
@.BlobB + @.IndexB) as FreeB
drop view sysf_spaceused
drop view sysi_spaceused
drop view syso_spaceused
--^v^-- End CursorProcessingDescription
fetch next from Databases
into @.dbName
end
close Databases
deallocate Databases
--^v^-- End CursorProcessingDescription
select * from #Sizes
drop table #Sizes
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
"Hassan" <hassan@.hotmail.com> wrote in message
news:u11AVVbYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> How can I get a query that i can run against a SQL Server to get a list of
> dbs and its size such as
> Name Total DB Size Allocated DB Usage Total Log Size
> Allocated Log Usage
> DB1 120GB 100GB 20GB 1GB
> DB2 1GB .7GB 50MB
> 25MB
> master 100MB 50MB 100MB 15MB
> ...etc..
> I would like to get a report in the same format as above.
> Thanks
> P.S. Using SQL 2000
>

Query to give me list of dbs + db size

How can I get a query that i can run against a SQL Server to get a list of
dbs and its size such as
Name Total DB Size Allocated DB Usage Total Log Size
Allocated Log Usage
DB1 120GB 100GB 20GB
1GB
DB2 1GB .7GB 50MB
25MB
master 100MB 50MB 100MB
15MB
...etc..
I would like to get a report in the same format as above.
Thanks
P.S. Using SQL 2000Hassan,
Try chewing on this for a while.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
declare
@.dbName sysname,
@.sqlStr varchar(8000)
declare
@.DataFileP dec(15), @.DataFileB dec(19),
@.LogFileP dec(15), @.LogFileB dec(19),
@.ReservedP dec(15), @.ReservedB dec(19),
@.UserHeapP dec(15), @.UserHeapB dec(19),
@.SysHeapP dec(15), @.SysHeapB dec(19),
@.TableClusterP dec(15), @.TableClusterB dec(19),
@.ViewClusterP dec(15), @.ViewClusterB dec(19),
@.BlobP dec(15), @.BlobB dec(19),
@.IndexP dec(15), @.IndexB dec(19)
declare
@.bpp smallint -- bytes per page
select @.bpp = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select
'dbName',
'DataFileB',
'LogFileB',
'ReservedB',
'UserHeapB',
'SysHeapB',
'TableClusterB',
'ViewClusterB',
'BlobB',
'IndexB',
'DataB',
'FreeB'
create table #Sizes (
[dbName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataFileB] [decimal](19, 0) NULL ,
[LogFileB] [decimal](19, 0) NULL ,
[ReservedB] [decimal](19, 0) NULL ,
[UserHeapB] [decimal](19, 0) NULL ,
[SysHeapB] [decimal](19, 0) NULL ,
[TableClusterB] [decimal](19, 0) NULL ,
[ViewClusterB] [decimal](19, 0) NULL ,
[BlobB] [decimal](19, 0) NULL ,
[IndexB] [decimal](19, 0) NULL ,
[DataB] [decimal](22, 0) NULL ,
[FreeB] [decimal](24, 0) NULL
)
--^v^--
--^v^-- Start CursorProcessingDescription
--^v^--
declare Databases cursor
static forward_only
for
select name
from master.dbo.sysdatabases
order by name
open Databases
fetch next from Databases
into @.dbName
while (@.@.FETCH_STATUS = 0) begin
set @.sqlStr = REPLACE('
create view sysf_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysfiles
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view sysi_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysindexes
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view syso_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysobjects
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
-- Get Data File Space
select @.DataFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 = 0)
set @.DataFileB = @.DataFileP * @.bpp
-- Get Log File Space
select @.LogFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 != 0)
set @.LogFileB = @.LogFileP * @.bpp
-- Get Reserved Space
select @.ReservedP = ISNULL(SUM(CONVERT(dec(15),reserved)), 0)
from sysi_spaceused
where indid in (0, 1, 255)
set @.ReservedB = @.ReservedP * @.bpp
-- Get User Heap Space
select @.UserHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id in (select id from syso_spaceused where type = 'u')
set @.UserHeapB = @.UserHeapP * @.bpp
-- Get System Heap Space
select @.SysHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id not in (select id from syso_spaceused where type = 'u')
set @.SysHeapB = @.SysHeapP * @.bpp
-- Get Table Clustered Index Space
select @.TableClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id not in (select id from syso_spaceused where type = 'v')
set @.TableClusterB = @.TableClusterP * @.bpp
-- Get Indexed View Clustered Index Space
select @.ViewClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id in (select id from syso_spaceused where type = 'v')
set @.ViewClusterB = @.ViewClusterP * @.bpp
-- Get Blob Space
select @.BlobP = ISNULL(SUM(CONVERT(dec(15),used)), 0)
from sysi_spaceused
where indid = 255
set @.BlobB = @.BlobP * @.bpp
-- Get Index Space
select @.IndexP = SUM(CONVERT(dec(15),used)) - @.UserHeapP - @.SysHeapP -
@.TableClusterP - @.ViewClusterP - @.BlobP
from sysi_spaceused
where indid in (0, 1, 255)
set @.IndexB = @.IndexP * @.bpp
-- Report Results
-- select
-- @.dbName as dbName,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.DataFileB), 1), '.00',
'') as DataFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.LogFileB), 1), '.00',
'') as LogFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB), 1), '.00',
'') as ReservedB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB), 1), '.00', '')
as HeapB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.TableClusterB), 1),
'.00', '') as TableClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ViewClusterB), 1),
'.00', '') as ViewClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.BlobB), 1), '.00', '')
as BlobB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.IndexB), 1), '.00', '')
as IndexB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB + @.TableClusterB +
@.ViewClusterB + @.BlobB), 1), '.00', '') as DataB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB - (@.HeapB +
@.TableClusterB + @.ViewClusterB + @.BlobB + @.IndexB)), 1), '.00', '') as FreeB
insert #Sizes
select
@.dbName as dbName,
@.DataFileB as DataFileB,
@.LogFileB as LogFileB,
@.ReservedB as ReservedB,
@.UserHeapB as UserHeapB,
@.SysHeapB as SysHeapB,
@.TableClusterB as TableClusterB,
@.ViewClusterB as ViewClusterB,
@.BlobB as BlobB,
@.IndexB as IndexB,
@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB + @.BlobB as
DataB,
@.ReservedB - (@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB +
@.BlobB + @.IndexB) as FreeB
drop view sysf_spaceused
drop view sysi_spaceused
drop view syso_spaceused
--^v^-- End CursorProcessingDescription
fetch next from Databases
into @.dbName
end
close Databases
deallocate Databases
--^v^-- End CursorProcessingDescription
select * from #Sizes
drop table #Sizes
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
"Hassan" <hassan@.hotmail.com> wrote in message
news:u11AVVbYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> How can I get a query that i can run against a SQL Server to get a list of
> dbs and its size such as
> Name Total DB Size Allocated DB Usage Total Log Size
> Allocated Log Usage
> DB1 120GB 100GB 20GB 1GB
> DB2 1GB .7GB 50MB
> 25MB
> master 100MB 50MB 100MB 15MB
> ...etc..
> I would like to get a report in the same format as above.
> Thanks
> P.S. Using SQL 2000
>

query to give me db sizes

Id like to have a query that gives me the all the total db data sizes such
as
DB1 100GB
DB2 25GBHassan,
This will give you some of the information you want:
exec sp_MSforeachdb N'exec ?..sp_spaceused'
Note that sp_MSforeachdb is undocumented and unsupported. Do
not use it in production code. Details about the system stored procedure
sp_spaceused can be found in Books Online.
Steve Kass
Drew University
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23pg5sXlXFHA.2540@.tk2msftngp13.phx.gbl...
> Id like to have a query that gives me the all the total db data sizes such
> as
> DB1 100GB
> DB2 25GB
>|||Hi,
Execute the below stored procedure from Query analyzer.
sp_databases
Thanks
Hari
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:OaOELKoXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> This will give you some of the information you want:
> exec sp_MSforeachdb N'exec ?..sp_spaceused'
> Note that sp_MSforeachdb is undocumented and unsupported. Do
> not use it in production code. Details about the system stored procedure
> sp_spaceused can be found in Books Online.
> Steve Kass
> Drew University
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23pg5sXlXFHA.2540@.tk2msftngp13.phx.gbl...
>

Friday, March 9, 2012

Query to count physician encounters

I need to count the total number of patient visits accumulated by each
provider in a medical practice between selected dates. I am using the table
of charges. Each visit may include several individual charges during the
visit, but I want to count the visit only once. Therefore I used this
statement (the date range is not actually hard coded):
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005' ;
What I get is:
Provider Code PatUniqueID service_date
-- -- --
RMB 25AAAAAA 01/01/2005
RMB 983AAAAA 02/01/2005
etc.
However, what I readlly want is a count. How many of these lines occur for
provider RMB and each provider. The desired table would be:
Provider Code Encounter Count
-- --
RMB 25
JDR 51
etc.
I am hoping to do this in a single SQL query statements, possibly with
imbedded SELECT statements. Can anyone help me to write this statement,
please.Richard,
If the query you show returns exactly the rows you want to count
(by Provider Code), you can build from this query:
select ProviderCode, count(*) as EncounterCount from (
SELECT DISTINCT ProviderCode, PatUniqueID, service_date
FROM Charges
WHERE service_date Between '01/01/1995' And '12/31/2005'
) as RowsToCount
group by ProviderCode
Steve Kass
Drew University
richardb wrote:

>I need to count the total number of patient visits accumulated by each
>provider in a medical practice between selected dates. I am using the table
>of charges. Each visit may include several individual charges during the
>visit, but I want to count the visit only once. Therefore I used this
>statement (the date range is not actually hard coded):
>SELECT DISTINCT ProviderCode, PatUniqueID, service_date
>FROM Charges
>WHERE service_date Between '01/01/1995' And '12/31/2005' ;
>What I get is:
>Provider Code PatUniqueID service_date
>-- -- --
>RMB 25AAAAAA 01/01/2005
>RMB 983AAAAA 02/01/2005
>etc.
>However, what I readlly want is a count. How many of these lines occur for
>provider RMB and each provider. The desired table would be:
>Provider Code Encounter Count
>-- --
>RMB 25
>JDR 51
>etc.
>I am hoping to do this in a single SQL query statements, possibly with
>imbedded SELECT statements. Can anyone help me to write this statement,
>please.
>
>|||Right! Got it and thanks...
"Steve Kass" wrote:

> Richard,
> If the query you show returns exactly the rows you want to count
> (by Provider Code), you can build from this query:
> select ProviderCode, count(*) as EncounterCount from (
> SELECT DISTINCT ProviderCode, PatUniqueID, service_date
> FROM Charges
> WHERE service_date Between '01/01/1995' And '12/31/2005'
> ) as RowsToCount
> group by ProviderCode
> Steve Kass
> Drew University
> richardb wrote:
>
>

Monday, February 20, 2012

query takes 90-120 sec compile/parse time

Hi,
We have a long query that is taking about 180 sec total time to execute and
out of that 12 sec is spent on compile/parse time. I added some index hints
etc and now it is taking only 2 sec to execute but now takes about 90 sec to
compile so total time is 92 sec. What can be the reason for such high compile
time?.....
Then i also add the force order hint to make sure sql server is not taking
to much time to get the optimal execution plan and in this case it still
takes 20 sec to compile(very high) and now takes long time to execute about
35 sec instead of 2 sec...
I also tried using maxdop 1 but that increases the compile time to 120
sec....
Note:
We can’t change this query to stored procedure.
Memory is enough on the machine
Second time for same parameters this query takes 0 compile time but in our
case we need to tune this query for first run so recompilation tuning doesn't
help.
There is no blocking involved as all this is reproducible with single user
on the machine...
Thanks
--Harvinder
What service pack are you on? What kind of hardware? How's your storage
space look? tempdb? Is this the only query you're running on it? It's
hard to even narrow the problem down without specifics...
BTW, your second-time run is probably because the query plan and some of the
tables/results are cached. If you cleared out the cache you might find the
time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
compilation.
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> Hi,
> We have a long query that is taking about 180 sec total time to execute
> and
> out of that 12 sec is spent on compile/parse time. I added some index
> hints
> etc and now it is taking only 2 sec to execute but now takes about 90 sec
> to
> compile so total time is 92 sec. What can be the reason for such high
> compile
> time?.....
> Then i also add the force order hint to make sure sql server is not taking
> to much time to get the optimal execution plan and in this case it still
> takes 20 sec to compile(very high) and now takes long time to execute
> about
> 35 sec instead of 2 sec...
> I also tried using maxdop 1 but that increases the compile time to 120
> sec....
> Note:
> We can't change this query to stored procedure.
> Memory is enough on the machine
> Second time for same parameters this query takes 0 compile time but in our
> case we need to tune this query for first run so recompilation tuning
> doesn't
> help.
> There is no blocking involved as all this is reproducible with single user
> on the machine...
> Thanks
> --Harvinder
>
|||service pack is 3a
this problem is reproducible on different set of hardware i.e. tried on
production (raid 10), development(raid 5)...all the machine have all the
file properly configured...
tempdb is also configured with about 20GB and almost all free....
this probelm can be reproducible with only this query running on machine....
i have never seen any query taking 90-120 sec just for compile time...
"Michael C#" wrote:

> What service pack are you on? What kind of hardware? How's your storage
> space look? tempdb? Is this the only query you're running on it? It's
> hard to even narrow the problem down without specifics...
> BTW, your second-time run is probably because the query plan and some of the
> tables/results are cached. If you cleared out the cache you might find the
> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
> compilation.
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
>
>
|||Whoa... is it taking 12 seconds to compile or 120 seconds to compile? Of
course I'd consider either one unacceptable, but there's a ten-fold
difference there. Is this a particularly complex query with a lot of JOINs?
Perhaps breaking it down into smaller pieces; i.e., VIEWs, or re-writing
with sub-queries, would help you out here. Have you run Profiler to see if
there's a bottleneck in the system somewhere?
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:EBDDDD47-15FB-4067-8B4F-33A3850FF4E8@.microsoft.com...[vbcol=seagreen]
> service pack is 3a
> this problem is reproducible on different set of hardware i.e. tried on
> production (raid 10), development(raid 5)...all the machine have all the
> file properly configured...
> tempdb is also configured with about 20GB and almost all free....
> this probelm can be reproducible with only this query running on
> machine....
> i have never seen any query taking 90-120 sec just for compile time...
> "Michael C#" wrote:

query takes 90-120 sec compile/parse time

Hi,
We have a long query that is taking about 180 sec total time to execute and
out of that 12 sec is spent on compile/parse time. I added some index hints
etc and now it is taking only 2 sec to execute but now takes about 90 sec to
compile so total time is 92 sec. What can be the reason for such high compile
time?.....
Then i also add the force order hint to make sure sql server is not taking
to much time to get the optimal execution plan and in this case it still
takes 20 sec to compile(very high) and now takes long time to execute about
35 sec instead of 2 sec...
I also tried using maxdop 1 but that increases the compile time to 120
sec....
Note:
We canâ't change this query to stored procedure.
Memory is enough on the machine
Second time for same parameters this query takes 0 compile time but in our
case we need to tune this query for first run so recompilation tuning doesn't
help.
There is no blocking involved as all this is reproducible with single user
on the machine...
Thanks
--HarvinderWhat service pack are you on? What kind of hardware? How's your storage
space look? tempdb? Is this the only query you're running on it? It's
hard to even narrow the problem down without specifics...
BTW, your second-time run is probably because the query plan and some of the
tables/results are cached. If you cleared out the cache you might find the
time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
compilation.
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> Hi,
> We have a long query that is taking about 180 sec total time to execute
> and
> out of that 12 sec is spent on compile/parse time. I added some index
> hints
> etc and now it is taking only 2 sec to execute but now takes about 90 sec
> to
> compile so total time is 92 sec. What can be the reason for such high
> compile
> time?.....
> Then i also add the force order hint to make sure sql server is not taking
> to much time to get the optimal execution plan and in this case it still
> takes 20 sec to compile(very high) and now takes long time to execute
> about
> 35 sec instead of 2 sec...
> I also tried using maxdop 1 but that increases the compile time to 120
> sec....
> Note:
> We can't change this query to stored procedure.
> Memory is enough on the machine
> Second time for same parameters this query takes 0 compile time but in our
> case we need to tune this query for first run so recompilation tuning
> doesn't
> help.
> There is no blocking involved as all this is reproducible with single user
> on the machine...
> Thanks
> --Harvinder
>|||service pack is 3a
this problem is reproducible on different set of hardware i.e. tried on
production (raid 10), development(raid 5)...all the machine have all the
file properly configured...
tempdb is also configured with about 20GB and almost all free....
this probelm can be reproducible with only this query running on machine....
i have never seen any query taking 90-120 sec just for compile time...
"Michael C#" wrote:
> What service pack are you on? What kind of hardware? How's your storage
> space look? tempdb? Is this the only query you're running on it? It's
> hard to even narrow the problem down without specifics...
> BTW, your second-time run is probably because the query plan and some of the
> tables/results are cached. If you cleared out the cache you might find the
> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
> compilation.
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> > Hi,
> >
> > We have a long query that is taking about 180 sec total time to execute
> > and
> > out of that 12 sec is spent on compile/parse time. I added some index
> > hints
> > etc and now it is taking only 2 sec to execute but now takes about 90 sec
> > to
> > compile so total time is 92 sec. What can be the reason for such high
> > compile
> > time?.....
> > Then i also add the force order hint to make sure sql server is not taking
> > to much time to get the optimal execution plan and in this case it still
> > takes 20 sec to compile(very high) and now takes long time to execute
> > about
> > 35 sec instead of 2 sec...
> > I also tried using maxdop 1 but that increases the compile time to 120
> > sec....
> >
> > Note:
> > We can't change this query to stored procedure.
> > Memory is enough on the machine
> > Second time for same parameters this query takes 0 compile time but in our
> > case we need to tune this query for first run so recompilation tuning
> > doesn't
> > help.
> > There is no blocking involved as all this is reproducible with single user
> > on the machine...
> >
> > Thanks
> > --Harvinder
> >
> >
>
>|||Whoa... is it taking 12 seconds to compile or 120 seconds to compile? Of
course I'd consider either one unacceptable, but there's a ten-fold
difference there. Is this a particularly complex query with a lot of JOINs?
Perhaps breaking it down into smaller pieces; i.e., VIEWs, or re-writing
with sub-queries, would help you out here. Have you run Profiler to see if
there's a bottleneck in the system somewhere?
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:EBDDDD47-15FB-4067-8B4F-33A3850FF4E8@.microsoft.com...
> service pack is 3a
> this problem is reproducible on different set of hardware i.e. tried on
> production (raid 10), development(raid 5)...all the machine have all the
> file properly configured...
> tempdb is also configured with about 20GB and almost all free....
> this probelm can be reproducible with only this query running on
> machine....
> i have never seen any query taking 90-120 sec just for compile time...
> "Michael C#" wrote:
>> What service pack are you on? What kind of hardware? How's your storage
>> space look? tempdb? Is this the only query you're running on it? It's
>> hard to even narrow the problem down without specifics...
>> BTW, your second-time run is probably because the query plan and some of
>> the
>> tables/results are cached. If you cleared out the cache you might find
>> the
>> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec
>> for
>> compilation.
>> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
>> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
>> > Hi,
>> >
>> > We have a long query that is taking about 180 sec total time to execute
>> > and
>> > out of that 12 sec is spent on compile/parse time. I added some index
>> > hints
>> > etc and now it is taking only 2 sec to execute but now takes about 90
>> > sec
>> > to
>> > compile so total time is 92 sec. What can be the reason for such high
>> > compile
>> > time?.....
>> > Then i also add the force order hint to make sure sql server is not
>> > taking
>> > to much time to get the optimal execution plan and in this case it
>> > still
>> > takes 20 sec to compile(very high) and now takes long time to execute
>> > about
>> > 35 sec instead of 2 sec...
>> > I also tried using maxdop 1 but that increases the compile time to 120
>> > sec....
>> >
>> > Note:
>> > We can't change this query to stored procedure.
>> > Memory is enough on the machine
>> > Second time for same parameters this query takes 0 compile time but in
>> > our
>> > case we need to tune this query for first run so recompilation tuning
>> > doesn't
>> > help.
>> > There is no blocking involved as all this is reproducible with single
>> > user
>> > on the machine...
>> >
>> > Thanks
>> > --Harvinder
>> >
>> >
>>

query takes 90-120 sec compile/parse time

Hi,
We have a long query that is taking about 180 sec total time to execute and
out of that 12 sec is spent on compile/parse time. I added some index hints
etc and now it is taking only 2 sec to execute but now takes about 90 sec to
compile so total time is 92 sec. What can be the reason for such high compil
e
time?.....
Then i also add the force order hint to make sure sql server is not taking
to much time to get the optimal execution plan and in this case it still
takes 20 sec to compile(very high) and now takes long time to execute about
35 sec instead of 2 sec...
I also tried using maxdop 1 but that increases the compile time to 120
sec....
Note:
We can’t change this query to stored procedure.
Memory is enough on the machine
Second time for same parameters this query takes 0 compile time but in our
case we need to tune this query for first run so recompilation tuning doesn'
t
help.
There is no blocking involved as all this is reproducible with single user
on the machine...
Thanks
--HarvinderWhat service pack are you on? What kind of hardware? How's your storage
space look? tempdb? Is this the only query you're running on it? It's
hard to even narrow the problem down without specifics...
BTW, your second-time run is probably because the query plan and some of the
tables/results are cached. If you cleared out the cache you might find the
time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
compilation.
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> Hi,
> We have a long query that is taking about 180 sec total time to execute
> and
> out of that 12 sec is spent on compile/parse time. I added some index
> hints
> etc and now it is taking only 2 sec to execute but now takes about 90 sec
> to
> compile so total time is 92 sec. What can be the reason for such high
> compile
> time?.....
> Then i also add the force order hint to make sure sql server is not taking
> to much time to get the optimal execution plan and in this case it still
> takes 20 sec to compile(very high) and now takes long time to execute
> about
> 35 sec instead of 2 sec...
> I also tried using maxdop 1 but that increases the compile time to 120
> sec....
> Note:
> We can't change this query to stored procedure.
> Memory is enough on the machine
> Second time for same parameters this query takes 0 compile time but in our
> case we need to tune this query for first run so recompilation tuning
> doesn't
> help.
> There is no blocking involved as all this is reproducible with single user
> on the machine...
> Thanks
> --Harvinder
>|||service pack is 3a
this problem is reproducible on different set of hardware i.e. tried on
production (raid 10), development(raid 5)...all the machine have all the
file properly configured...
tempdb is also configured with about 20GB and almost all free....
this probelm can be reproducible with only this query running on machine...
.
i have never seen any query taking 90-120 sec just for compile time...
"Michael C#" wrote:

> What service pack are you on? What kind of hardware? How's your storage
> space look? tempdb? Is this the only query you're running on it? It's
> hard to even narrow the problem down without specifics...
> BTW, your second-time run is probably because the query plan and some of t
he
> tables/results are cached. If you cleared out the cache you might find th
e
> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
> compilation.
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
>
>|||Whoa... is it taking 12 seconds to compile or 120 seconds to compile? Of
course I'd consider either one unacceptable, but there's a ten-fold
difference there. Is this a particularly complex query with a lot of JOINs?
Perhaps breaking it down into smaller pieces; i.e., VIEWs, or re-writing
with sub-queries, would help you out here. Have you run Profiler to see if
there's a bottleneck in the system somewhere?
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:EBDDDD47-15FB-4067-8B4F-33A3850FF4E8@.microsoft.com...[vbcol=seagreen]
> service pack is 3a
> this problem is reproducible on different set of hardware i.e. tried on
> production (raid 10), development(raid 5)...all the machine have all the
> file properly configured...
> tempdb is also configured with about 20GB and almost all free....
> this probelm can be reproducible with only this query running on
> machine....
> i have never seen any query taking 90-120 sec just for compile time...
> "Michael C#" wrote:
>