Peformance problem

Question

Peformance problem

xgcmcbain

Default port

Points: 1454
More actions
March 19, 2009 at 2:09 pm

#202448

I have a table, with nvarchar(50) of 40 million records, this field has an unique index on it
The table itself has an id field, which is the primary key, and is identity
But
my query is
Select count(*) from table where not thedate is null
The table does have a non unique index on thedate already, the query is taking 15 minutes plus and i cancelled it.
My ram situation could be an issue, im only giving SQL Server 1.8 gigs of ram, since my system alone only has 3.0 gigs
Anyone know how i can get fast results from this table, i mean i have an index on the field. Is it the fact i allowed nulls?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

umar iqbal SSCommitted Points: 1703 More actions · Answer 1

did u monitor your disk activity durin query. Like disk queue length. can you monitor disk queue and memory. perfmon wil ltell you if any bottle neck happening. Besides that what raid you are using for data file? if possible put that particular tableon diffrent LUN. but first you have to see bottle neck in resources you have. once confirmed you can go ahead. i am assuming you are using sql 2005 with latest patch.

hint.

run tracer and see what else is running in sql. open task manager and see cpu activity.

:crazy: :alien:

Umar Iqbal

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Table definition and index definitions please. Also the execution plan, saved as a .sqlplan file, zipped and attached.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004254 More actions · Answer 3

Select count(*) from table where thedate > 0

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ashok.S SSC Eights! Points: 869 More actions · Answer 4

Have you tried with NOLOCK option? It may be bit faster than the usual one.

Regards,

Ashok S

David Betteridge Hall of Fame Points: 3264 More actions · Answer 5

Is using NOLOCK a good idea? Quite a lot of people post sql on this site where they are specifying it, but I have always avoided its use.

My understanding is that

1. It doesn't take out any database locks so giving faster performance

2. It doesn't get blocked by any current database locks, again faster performance

(Point 2 can be useful if you wish to view the process of a long running query)

However

1. The results could return data from transactions which haven't yet commitment and so could be rolled-back.

2. The results could be inconsistent if the other connection is mid-write. (I'm might be wrong on this point)

Regards

David

Ashok.S SSC Eights! Points: 869 More actions · Answer 6

[font="Verdana"]Hi David,

What you told is correct, I agree on it. NOLOCK will return the data faster, but it will fetch the data which haven't commited and so data will be incosistent.

But, as you said it will avoid the other database lock. I thought his query is taking time because of other table locks.

Anyhow, thanks for sharing this info, which may be useful for the guy who posted it also for all who read this post 🙂

Regards,

Ashok S[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

David (3/20/2009)
However
1. The results could return data from transactions which haven't yet commitment and so could be rolled-back.

Correct

2. The results could be inconsistent if the other connection is mid-write. (I'm might be wrong on this point)

No. For actual page writes, SQL takes exclusive latches on the pages. Even a query with nolock cannot read an exclusively latched page.

It is possible to see data as it is between two data changes in a transaction. Say for accounts, if the insert of the debit and the insert of the credit (to a different account) are done as two inserts in a transaction, a query with nolock could see the accounts table after the debit but before the credit.

There's also the (slight) possibility of duplicate or missing rows due to nolock. Read uncommitted isolation essentially means to SQL 'Get it now, I don't mind if it's slightly wrong'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

xgcmcbain Default port Points: 1454 More actions · Answer 8

im sorry guys, i found out im an idiot on my own. In the sql management tool, there is a play button (which is debug) and an execute button, sometimes i will hit play, which was the problem

for us old schoolers, that was ok in query analyzer i believe from what i can remember

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 9

LOL no worries! I've hit that thing too. Thanks to Microsoft for putting it right next to Execute.