Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Performance issue

Author  Topic 

WindChaser
Posting Yak Master

225 Posts

Posted - 2014-08-19 : 18:26:11
Hi folks,

I've made a few observations that I'm not too clear on. I have a table of clients that only contains about 4000 entries, so it's a tiny table really. If I run a program which connects to the DB and do an initial query on the database (e.g. select * from Clients where account = 1), then the query takes an incredible 5 seconds to be executed. If I redo the same query afterwards, it's nearly instantaneous. If I close the program and launch it again, same observation. Is this due to a buffer or cache effect of some kind? If so, can I optimize it?

Otherwise, could this be due to lack of index maintenance (because, in all honesty, I haven't done any)? On that topic, I understand that a reorg is best for <30% fragmentation and a rebuild for >30% fragmentation, probably to obtain good results in minimum time. However, none of the tables in my DBs have more than perhaps 200,000 records, so could I just indiscriminently rebuild all indices and afterwards run a sp updatestats?

Any insight would be greatly appreciated. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-19 : 19:17:21
You could rebuild all indexes (update stats would not be needed), but it might be overkill. I'd instead update stats more frequently and rebuild/reorg indexes less frequently.

When you say you close the program and "same observation", are you saying it's slow again and then fast? Or is it still fast? If it's still fast, then yes it's because it's in cache. If it's slow again, then I might suspect a bad plan. Need more info first.

What does your Page Life Expectancy look like? Is this a shared server? How big is the database? How much RAM is the SQL instance configured to use? Are there other databases on the same SQL instance? What does total/target memory look like?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2014-08-19 : 21:10:10
Hi Tara!

By same observation, I meant that if I close the program and relaunch it, the first query will take 5 seconds and subsequent queries will be instantaneous. In other words, the SQL Server response is based on the process. If I kill a process and open another different process that queries the SQL Server, the first query coming out of that process will take a long time. I have no idea why. Nonetheless, it is clearly observable and reproducible.

Server is a dedicated station hosting a SQL Server Express, not networked for testing purposes. DB mdf file is 24MB in size, ldf is currently 23MB in size. DB properties show that overall DB size is 47MB, space available is 1.25MB, number of users = 4 (what the heck is this?), and both files can autogrow by 10% with unrestricted growth. Instance properties are 2147483647MB of maximum server memory, 1024KB minimum memory per query, current operating system memory is 4GB. There are 10 DBs running on the instance for a total of 156MB of current mdf file size being taken up on disk, so pretty small stuff.

Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-20 : 05:11:03
space available is 1.25MB?

We are the creators of our own reality!
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2014-08-20 : 11:50:01
That's automated. All my DBs are set to autogrow as the need arises.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 12:35:54
Is the autoclose option enabled for the database? I bet that's it given the symptoms.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2014-08-20 : 15:55:48
Tara,

The following are the properties of the database:
AutoClose = False
AutoCreateStats = True
AutoShrink = True
AutoUpdateStatistics = True
AutoUpdateStatisticsAsynchronously = False

Naturally, when the program is closed, it goes through a clean-up routine and closes all connections to the databases. When the program is reopened, it opens all required connections at startup. By the time a query is made, the connections have long ago been opened.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 16:08:05
Set AutoShrink to False. It is not the culprit here, but that should never be enabled.

You'll need to compare execution plans at this point. You can do this by running a trace.

Does the program free the procedure cache and/or drop the clean buffers at startup or when closing?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2014-08-20 : 22:53:48
Tara, I have no idea how I could do that within a program. It's not like doing it in SSMS... You can't run a trace as a dataset or a recordset, so I don't know how I could actually collect the results.

The program does not address cache or buffers at all.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 12:28:55
The trace is done in SQL Profiler or Management Studio (server-side trace). It is not done in your program.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -