| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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. |
|
|
|
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! |
|
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2014-08-20 : 15:55:48
|
| Tara,The following are the properties of the database:AutoClose = FalseAutoCreateStats = TrueAutoShrink = TrueAutoUpdateStatistics = TrueAutoUpdateStatisticsAsynchronously = FalseNaturally, 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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Performance issue