Query Would Run For Ever - No Clue

Hello fellow DBAs

I have a strange situation here. I am executing a SQL query which runs for ever till it fills up all the available temp space.

The same query runs within 1 minute in another database on another server. That database is a development database but with same records (and data).

I tried the following:

UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG

Nothing helps... what should I do next?post the ddl + the query so we can see. Its common to have a rogue/running away query that would take the server down to its knee.

e.g.
select *
from master..syscolumns,master..syscolumns,master..sysc olumns,master..syscolumns

Query window close automatically

It's very strange!! When i am querying SQL database via SQL Query Analyzer,
the query window sometimes close itself automatically without any notice.
There is no infomation in system event log.
Meanwhile, my other colleague can use the tool to query database without any
problem.
I am using SQL2000 SP3 on XP (SP1).
Is there anyone encountered the same problem? Could u please give us some
advise?
Thanks in Advance
Luke
Strange. I have not experienced this behavior.
Keith
"Luke" <luke.sheng@.sapb.com.cn> wrote in message
news:uMzEiShmEHA.2020@.TK2MSFTNGP09.phx.gbl...
> It's very strange!! When i am querying SQL database via SQL Query
Analyzer,
> the query window sometimes close itself automatically without any notice.
> There is no infomation in system event log.
> Meanwhile, my other colleague can use the tool to query database without
any
> problem.
> I am using SQL2000 SP3 on XP (SP1).
> Is there anyone encountered the same problem? Could u please give us some
> advise?
> Thanks in Advance
> Luke
>
sql

Query VS Stored Procedure problem

Hello.

I am having a strange problem with SQL Server 2005. I have written a SELECT query that contains unions, joins and group functions. when the sql query is run using t-sql statements, the query completed execution in about 10-12 seconds. When the same query is written in a stored procedure without making any changes in the SELECT query (only adding a date parameter), it does not generate any result.

I waited for about 1 hour for the stored procedure to give me the result but it did not. Can anyone help me out with this problem?

Thanks in advance.

Raza:

Please provide a listing of your stored procedure.

Dave

|||

Definitely you need to provide the query, but also how much data is involved. Definitely look at the plans of the query (post them here too using set showplan_text on to get the plan) for clues as to what might be happening.

|||The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

I have MS Sql 2005 64 bit Enterprise Edition with SP1 installed.

here is the query.

SELECT sim.DEALER_CODE, sim.TRANSACTION_STAMP, sid.PRODUCT_CODE, dbo.REFERENCE_PRODUCT_CODES.SALE_PRICE AS UNIT_PRICE, sid.AMOUNT, sid.QUANTITY, sim.INVOICE_NUMBER, dbo.REFERENCE_TRANSACTION_TYPES.DESCRIPTION AS TRANSACTION_TYPE, sim.TRANSACTION_USER, icl.LOCATION_CODE, icl.REGION_NAME, icl.COUNTRY, (CASE WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'I' THEN 'D' WHEN dbo.REFERENCE_DEALER_CODES.DEALER_TYPE = 'N' THEN 'D' ELSE 'E' END) AS DEALER_TYPE, sim.PARAMETER_1 AS ITEM_SERIAL FROM dbo.SALES_INVOICE_DETAIL AS sid INNER JOIN dbo.SALES_INVOICE_MASTER AS sim ON sid.INVOICE_NUMBER = sim.INVOICE_NUMBER INNER JOIN dbo.VIEW_USER_INFORMATION_COUNTRY_LEVEL AS icl ON sim.TRANSACTION_USER = icl.USER_ID INNER JOIN dbo.REFERENCE_TRANSACTION_TYPES ON sim.TRANSACTION_TYPE = dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE INNER JOIN dbo.REFERENCE_PRODUCT_CODES ON sid.PRODUCT_CODE = dbo.REFERENCE_PRODUCT_CODES.PRODUCT_CODE LEFT OUTER JOIN dbo.REFERENCE_DEALER_CODES ON sim.DEALER_CODE = dbo.REFERENCE_DEALER_CODES.DEALER_CODE WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

|||

Some suggestions to identify the problem:

1- Limit the number of rows returned (maybe by adding an extra predicate) and see if the sproc returns any results at all. If the sproc is still hanging, it maybe an urelated issue with the query.

2- If the sproc returns results, try to open a cursor on the original query and print messages after every fetch to verify the query is returning results inside the proc.

Thanks.

|||

What do you mean "doesn't return any result" Do you mean it takes forever, or it returns no rows?

So the procedure is:

create procedure procName
as

<your query>

go

Or is there anything else? I don't know why that wouldn't use as good of a plan as an ad hoc query...especially if you recompile the procedure.

|||You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + '

23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))|||

obviously probelm is with the date. remove the date from SP and verify.

Can you explain what is your requierment on date field

|||Could you verify whether there any records which satisfies the date condition mentioned in the where clause?|||

Tom Phillips wrote:

You are using between to compare a string. This NEVER works.

Try this:

WHERE (sim.TRANSACTION_STAMP BETWEEN CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) AS DATETIME) AND CAST(CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AS DATETIME) AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Tom,

That is not true. BETWEEN works with string values, the problem is that it is more difficult to anticipate the results, and a greater reliance upon good indexing. For example, try these two queries:

USE Northwind
GO

SELECT
EmployeeID,
LastName,
FirstName
FROM Employees
WHERE LastName BETWEEN 'a' AND 'f'

SELECT
OrderID,
OrderDate
FROM Orders
WHERE OrderDate BETWEEN cast( convert( char(10), getdate() - 3850, 101 ) AS datetime )
AND ( cast( convert( char(10), getdate() - 3800, 101 ) AS datetime ) + ' 23:59:59' )

|||If the query works as an ad hoc call, but not in a procedure, it is unlikely that there is anything
"wrong" with the query itself. There is something missing that needs to be supplied before we can make a judgment. Maybe a param or something... Or an IF...THEN around the query. We need to see the entire proc...|||

RazaRana wrote:

The data involved is huge (millions of rows) but regardless the sql statements copied from the proc and written in query window returns result in 5 - 7 seconds and when the same proc is executed it doesnot return any result.

...

WHERE (sim.TRANSACTION_STAMP BETWEEN CONVERT(CHAR(10), GETDATE() - 1, 101) AND CONVERT(CHAR(10), GETDATE() - 1, 101) + ' 23:59:59') AND (NOT (dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE IN ('2', '7')))

Thanks

I don't think that this WHERE clause is correct and will work to return data. I suggest the following alteration:

WHERE ( sim.TRANSACTION_STAMP BETWEEN convert( char(10), getdate() - 1, 101 )
AND convert( char(10), getdate() - 1, 101 ) + ' 23:59:59' )
AND dbo.REFERENCE_TRANSACTION_TYPES.TRANSACTION_TYPE NOT IN ( '2', '7' )
)

|||You are correct, it technically "works". I should have said "NEVER gives the expected results". :)

This is the 3rd time in 3 months I have seen someone trying to do this exact same WHERE clause with BETWEEN a date and 2 strings.|||Hi

Sorry for late reply. The query works perfectly fine and returns results (upto 15,000 rows) in less than 10 secs.

I use the same query in sproc, only the date is passed as a parameter. The sproc takes forever, i waited for 1 hr and 25 minutes and still no results.

The interesting thing is that i killed a few locks created on the tempdb and ran the sproc at midnight and it returned the results in about 10 secs.

Thanx

Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER12 40 KB8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWERdbouser table2004-11-12 17:31:05.130
ID intno4
NAME varcharno50
URL varcharno1024
PARTNERNAMEvarcharno50
CACHEPERIODintno4
BEINGUPDATEDbitno1
ADDITIONALURLvarcharno512
UPDATEBEGINDATEdatetimeno8
OSPRK_osusr_131_PREVIEWERclustered, unique, primary key located on PRIMARYID
PRIMARY KEY (clustered)OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
Jorge
Hi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>
|||Is the only index you have the index created by the primary key? If so, you probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any non-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>
|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of a
permanent resolution by understanding what is causing so many locks in such a
small table.
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:

> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER12 40 KB8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWERdbouser table2004-11-12 17:31:05.130
> ID intno4
> NAME varcharno50
> URL varcharno1024
> PARTNERNAMEvarcharno50
> CACHEPERIODintno4
> BEINGUPDATEDbitno1
> ADDITIONALURLvarcharno512
> UPDATEBEGINDATEdatetimeno8
> OSPRK_osusr_131_PREVIEWERclustered, unique, primary key located on PRIMARYID
> PRIMARY KEY (clustered)OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER _PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER _PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER _PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>
|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
[vbcol=seagreen]
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on the
> current table. While this can be a temporary solution, you need to think of a
> permanent resolution by understanding what is causing so many locks in such a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:

Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
ID int no 4
NAME varchar no 50
URL varchar no 1024
PARTNERNAME varchar no 50
CACHEPERIOD int no 4
BEINGUPDATED bit no 1
ADDITIONALURL varchar no 512
UPDATEBEGINDATE datetime no 8
OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY
ID
PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
JorgeHi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Is the only index you have the index created by the primary key? If so, you
probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any n
on-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one tab
le
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem
.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMAR
Y ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of
a
permanent resolution by understanding what is causing so many locks in such
a
small table.
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:

> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one tab
le
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem
.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMAR
Y ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131
_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131
_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131
_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
[vbcol=seagreen]
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on th
e
> current table. While this can be a temporary solution, you need to think o
f a
> permanent resolution by understanding what is causing so many locks in suc
h a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:
>

Query time execution to long

Hello,
I've got a very dificult or strange troubleshoot in my hands. I've one table
that is very small in my database.
sp_spaceused 'osusr_131_PREVIEWER'
Name Rows Reserved Data
Index_size Unused
osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
query execution time to return the final result its 2 minutes. I know that
this table its locked a lot of times, but i cant turn arround this problem.
I send a description of my table:
osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
ID int no 4
NAME varchar no 50
URL varchar no 1024
PARTNERNAME varchar no 50
CACHEPERIOD int no 4
BEINGUPDATED bit no 1
ADDITIONALURL varchar no 512
UPDATEBEGINDATE datetime no 8
OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
Table is referenced by foreign key.
OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
OutSystems.dbo.osusr_131_PREVIEWER_CONT:
OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
OutSystems.dbo.osusr_131_SITE_STRUCTURE:
OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
Have you any idea to resolve this situation?
Thanks and best regards,
JorgeHi
Try to create an indexed view to improve a performance
This script has written by Steve Kass.
I'm sure that gives you an idea
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one
table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this
problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on
PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Is the only index you have the index created by the primary key? If so, you probably need to analyze
the queries using this table and add a few good non-clustered indexes. Any non-clustered index will
support SELECT COUNT(*), the more narrow the column, the more efficient.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4822F0DE-DBB1-4516-B2E7-B4E35F92A5F3@.microsoft.com...
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Hi,
Probably you can use the table hint like NOLOCK to avoid the locking on the
current table. While this can be a temporary solution, you need to think of a
permanent resolution by understanding what is causing so many locks in such a
small table.
--
- - - - - - - - -
Thanks
Yogish
"CC&JM" wrote:
> Hello,
> I've got a very dificult or strange troubleshoot in my hands. I've one table
> that is very small in my database.
> sp_spaceused 'osusr_131_PREVIEWER'
> Name Rows Reserved Data
> Index_size Unused
> osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> query execution time to return the final result its 2 minutes. I know that
> this table its locked a lot of times, but i cant turn arround this problem.
> I send a description of my table:
> osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> ID int no 4
> NAME varchar no 50
> URL varchar no 1024
> PARTNERNAME varchar no 50
> CACHEPERIOD int no 4
> BEINGUPDATED bit no 1
> ADDITIONALURL varchar no 512
> UPDATEBEGINDATE datetime no 8
> OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> Table is referenced by foreign key.
> OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> Have you any idea to resolve this situation?
> Thanks and best regards,
> Jorge
>
>|||Thanks everyboy for you're help.
Best regards,
Jorge
"Yogish" wrote:
> Hi,
> Probably you can use the table hint like NOLOCK to avoid the locking on the
> current table. While this can be a temporary solution, you need to think of a
> permanent resolution by understanding what is causing so many locks in such a
> small table.
> --
> - - - - - - - - -
> Thanks
> Yogish
>
> "CC&JM" wrote:
> > Hello,
> >
> > I've got a very dificult or strange troubleshoot in my hands. I've one table
> > that is very small in my database.
> >
> > sp_spaceused 'osusr_131_PREVIEWER'
> > Name Rows Reserved Data
> > Index_size Unused
> > osusr_131_PREVIEWER 12 40 KB 8 KB 32 KB 0 KB
> >
> > When i execute one 'select count(*) from osusr_131_PREVIEWER', the minimum
> > query execution time to return the final result its 2 minutes. I know that
> > this table its locked a lot of times, but i cant turn arround this problem.
> >
> > I send a description of my table:
> >
> > osusr_131_PREVIEWER dbo user table 2004-11-12 17:31:05.130
> >
> > ID int no 4
> > NAME varchar no 50
> > URL varchar no 1024
> > PARTNERNAME varchar no 50
> > CACHEPERIOD int no 4
> > BEINGUPDATED bit no 1
> > ADDITIONALURL varchar no 512
> > UPDATEBEGINDATE datetime no 8
> >
> > OSPRK_osusr_131_PREVIEWER clustered, unique, primary key located on PRIMARY ID
> >
> > PRIMARY KEY (clustered) OSPRK_osusr_131_PREVIEWER
> >
> > Table is referenced by foreign key.
> > OutSystems.dbo.osusr_131_HOMEPAGE_STRUC:
> > OSFRK_osusr_131_HOMEPAGE_STRUC_OSUSR_131_PREVIEWER_PREVIEWER
> > OutSystems.dbo.osusr_131_PREVIEWER_CONT:
> > OSFRK_osusr_131_PREVIEWER_CONT_OSUSR_131_PREVIEWER_PREVIEWERID
> > OutSystems.dbo.osusr_131_SITE_STRUCTURE:
> > OSFRK_osusr_131_SITE_STRUCTURE_OSUSR_131_PREVIEWER_PREVIEWERID
> >
> > Have you any idea to resolve this situation?
> >
> > Thanks and best regards,
> > Jorge
> >
> >
> >