SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Performance issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WindChaser
Posting Yak Master

225 Posts

Posted - 08/19/2014 :  18:26:11  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 08/19/2014 :  19:17:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/19/2014 :  21:10:10  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  05:11:03  Show Profile  Reply with Quote
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 - 08/20/2014 :  11:50:01  Show Profile  Reply with Quote
That's automated. All my DBs are set to autogrow as the need arises.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 08/20/2014 :  12:35:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/20/2014 :  15:55:48  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 08/20/2014 :  16:08:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/20/2014 :  22:53:48  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 08/21/2014 :  12:28:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000