query taking too much time

Question

query taking too much time

Viewing 11 posts - 16 through 25 (of 25 total)

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

bohra_anand SSC-Addicted Points: 473 More actions · Answer 1

ALZDBA (7/18/2008)
- 200mb for sqlserver isn't that much.

pl see the attached file where at one time my sqlserver.exe size goes to 113 mb & in next process it goes to 200+ mb & query is not so complicated

select sum(chg_in_oi)/100000 from tblfnobhav where datep='2008-07-18' and nsecode='nifty' and option_type='CE'

select sum(chg_in_oi)/100000 from tblfnobhav where datep='2008-07-18' and nsecode='nifty' and option_type='PE'

Johan Bijnens SSC Guru Points: 135379 More actions · Answer 2

Oh indeed, sqlserver will try to make its work as easy as possible by grabbing all the ram it actualy needs.

Probably it decided to grab some more because of the workload it recently had ?? (maybe Gail has more info on this)

I have 512 mb ram on my pc & its p4 3 ghz

- On our pc/laptops we advise at least 2Gb if running sqlserver localy.

- For developers having visual studio 2005 we advise at least 1Gb.

- Those working with a virtual pc local, we advise >2Gb

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

ALZDBA (7/18/2008)
And sweet is the odor of victory 😎
But sometimes no one acknowleges it ... "after all you just did your job".

The best part was that a few months later I discovered that the vendor had taken my index suggestions and rolled them out to all their SQL Server customers as a performance-related patch that *their* dedicated engineers had created.

:angry:

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

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

bohra_anand (7/18/2008)
pl see the attached file where at one time my sqlserver.exe size goes to 113 mb & in next process it goes to 200+ mb & query is not so complicated

SQL Server loves memory and will take as much as it can if it's not limited. If you want to restrict the amount of memory SQL uses (and on a workstation I would recommend you do that) set the server's max memory.

Please note that SQL 2005's minimum memory requirement is 512 MB. You really need more memory on that machine. My current desktop and laptop both have 2 GB and my new desktop has 4 GB.

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

bohra_anand SSC-Addicted Points: 473 More actions · Answer 5

Hi,

Thanks for all your replies.

I am using SQL only as a data backup & bulk data storage.

This sql is only for my local use & it does not have any frontend software for it which use SQL as backend.

Only frontend software I use is EXCEL which transfer data to sql & get data from sql through ADODB connection (in excel VBA).

so pl tell me option where I can limit SQL max memory so that my whole pc would not get affected also many a times I get time out error while extracting data from SQL.

happy to receive help & suggestions.

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

Restricting SQL's memory is not going to solve timeouts. It will make them worse. The problem is that you have a lot of data and no (or very few) indexes on it, as well as a PC that's got very little memory by modern standards.

In management studio, open object explorer, right click on the server and select properties. Go to the memory tab and set the max memory. 200MB is probably the absolute minimum that it should be set to.

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

bohra_anand SSC-Addicted Points: 473 More actions · Answer 7

should I create Index on all fields?

will this speed up my query retrievals??

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

Maybe, maybe not

You need to look at the queries you are running and create indexes to support thos queries. Look at what columns you are regularly filtering on and create indexes on those columns.

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

bohra_anand SSC-Addicted Points: 473 More actions · Answer 9

Thank you for your reply.

& one more question.

the earlier code given for index will suffice or should I go for advanced filtering like cluster or whatever.

but to be frank I really don't know about the same.:D:D

just seen while creating index through sql server enterprise manager

earlier index code was

CREATE

INDEX [Indx_datep] ON [dbo].[tblfnobhav] ([DATEP] desc )

any help or link explaining index & their usage will be highly appreciated.

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

There are a couple links on this thread:

http://www.sqlservercentral.com/Forums/Topic537433-149-1.aspx

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

Johan Bijnens SSC Guru Points: 135379 More actions · Answer 11

GilaMonster (7/21/2008)
ALZDBA (7/18/2008)
And sweet is the odor of victory 😎
But sometimes no one acknowleges it ... "after all you just did your job".
The best part was that a few months later I discovered that the vendor had taken my index suggestions and rolled them out to all their SQL Server customers as a performance-related patch that *their* dedicated engineers had created.
:angry:

That rings a bell ...

I've been in a struggle with our ERP systems vendor to implement DRI and FK indexes a couple of years ago.

I took them 3 years :doze: before they finaly implemented DRI and the FK indexes.

Now their migrations are more stable, applications quit faster, ...

Now, if only I could get them to support full recovery model.... :Whistling:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me