Also, if you find yourself building a queue, you should look at Service Broker which is a transacted queue that's already properly designed.
Regards, Greg From: [email protected] [mailto:[email protected]] On Behalf Of David Ames Sent: Wednesday, 23 June 2010 12:10 PM To: ozDotNet Subject: RE: Embedding SQL index hints into SQL commands for SQL SERVER. Peter, It sounds like the original intent of the design is a form of "message queue" or processing queue. As the basis of the select statement is a couple of bit fields, I'm not sure that indexing them would make much of a difference - There is not much distribution of values if the only possible values are true/false. If the records in the "queue" do need to be kept indefinitely then SQL 2k8 supports Filtered Indexes which would be ideal for this scenario. Dave From: [email protected] [mailto:[email protected]] On Behalf Of Maddin, Peter Sent: Wednesday, 23 June 2010 11:41 AM To: ozDotNet Subject: RE: Embedding SQL index hints into SQL commands for SQL SERVER. Thanks It is not the inserting of records into the database that is a problem. This is in fact lightening fast. The problem is obtaining a record set of data to be forwarded using a select statement. The table uses bit flags to determine which records need to be forwarded as apposed from those that have been already forwarded. For several years, the select statement has always returned its result set with 30 seconds. There are a two sets bit flags which are used to determine where the data is to be forwarded to. One query is working fine (for the present), the other is timing out. I could extend the timeout but eventually it will again timeout as the database grows in size. The database contains many historical records that are literally years old. There is no need to these to be kept and these can be deleted. The select statement returns a Text blob field which has data that can be several kilobytes in size. I have removed this from the select statement and will be selecting this for each row of the select statement returned. Regards Peter Maddin Applications Development Officer PathWest Laboratory Medicine WA Phone : +618 9473 3944 Fax : +618 9473 3982 E-Mail : [email protected] The contents of this e-mail transmission outside of the WAGHS network are intended solely for the named recipient's), may be confidential, and may be privileged or otherwise protected from disclosure in the public interest. The use, reproduction, disclosure or distribution of the contents of this e-mail transmission by any person other than the named recipient(s) is prohibited. If you are not a named recipient please notify the sender immediately. From: [email protected] [mailto:[email protected]] On Behalf Of Corneliu I. Tusnea Sent: Tuesday, 22 June 2010 12:03 PM To: ozDotNet Subject: Re: Embedding SQL index hints into SQL commands for SQL SERVER. Peter, As everyone here seems to suggest your hint for the query is not what you are looking for just like everyone else said. 6000 requests a day on a 20Gb database is peanuts for any sql server. That's like 12 requests per minute within the 8h working time when most of the databases can cope with few hundred requests a second. Can you post here the schema definition for your two tables and the indexes that are defined? Some other recommendations: 1. Rebuild your indexes (it won't impact select performance too much but it will help) 2. Try to reduce your database/table sizes. (backup, shrink, change backup model to simple, backup again, shrink, backup, restore) google for other versions of the same task. 3. Check if you don't have disk errors that can make sql slower (dbcc checkdb) and checkdsk. 3. Verify data conversions (size, type or collation) between columns used in join clauses. Sql is not always very happy about that. e.g. P.PracticeCode is varchar(20) with one collation while R.PractiveCode is nvarchar(30) with a different collation. 4. Is your "results" table in there a view? Maybe that is the slow(er) bit! Maybe you have a lovely and popular DISTINCT in that view. 5. Verify you have a Clustered Index on P.PracticeCode and a Non-clustered Index on R.PracticeCode 6. Verify that you don't have more that one such index on the same columns) 7. Run your query with the Show Actual Execution Plan. Just some thoughts. Corneliu. On Tue, Jun 22, 2010 at 1:13 PM, Greg Low (greglow.com) <[email protected]> wrote: Hi Peter, In general, if the indexing is ok, the amount of data in the table (within reason) is not really an issue. That's still a *relatively* small table. As an example, we have one client in Melbourne with a 6TB table. The only time the amount of data affects you is if you're reading the whole thing for some reason. If the DBA has added an index and thinks you need a hint to make your query use it, it's highly likely that it's the wrong index that he/she has built. What is the definition of the index that has been built for this query? If you can post the DDL for the two tables and the indexes, we can probably help further. Regards, Greg From: Maddin, Peter [mailto:[email protected]] Sent: Tuesday, 22 June 2010 11:12 AM To: [email protected]; ozDotNet Subject: RE: Embedding SQL index hints into SQL commands for SQL SERVER. Thanks Greg and everyone else. I designed the database which has the simplest schema of any database I have ever designed. Its only a small but critical part of the whole system. It operated quite well for several years and performance problems have only become evident since the database started approaching 20GB. The database serves as a store and forward repository. It allows for the caching of data so should anything go awry with what has been forwarded, there is the opportunity for some data to be set to be forwarded again. The required period of time to keep forwarded data is probably only for a year or so. That was my intention. At the commencement of the project there was the intention to purge the database on a regular basis but the powers that be, wanted it moved into production ASAP so purging of the database was deferred. Once the system was moved into production the need to remove very old unnecessary records never happened and it just grew despite my reminders that this still needed to be done. There is now data that dates back to before 2004. When first moved into production the system handled about 1000 requests a day. The current though put is around 6000. Now that it has exceeded 25GB in size there are timeouts on some select statements and the accumulative affect of these is having a detrimental impact on the system as a whole. If they want to use the system as an archive of historical data that had been forwarded to clients, then the schema would have been designed with this mind however this was not the intended purpose of the system. This has been recognized and the database will be purged. The other solution is to vertically partition the primary table which stores the data to be forwarded. The issue that I had is the person responsible for looking after the system in production (I called them a DBA but I do not know how qualified they are to wear this title) said that the problem was in the application code not the database and I need to amend the SQL commands to force the database engine to use a new index they had added. Change Select R.PracticeCode, P.PracticeName, R.DoctorCode, R.SiteCode, R.Online, R.OnLineSent, R.Stored, R.ReportType, R.DoctorFileName, R.PracticeFileName, R.LastFailure, R.Results from results as R inner join Practice as P on R.PracticeCode = P.PracticeCode where ((R.Online = 1) and (R.OnlineSent = 0)) Order by R.PracticeCode, R.Stored to Select R.PracticeCode, P.PracticeName, R.DoctorCode, R.SiteCode, R.Online, R.OnLineSent, R.Stored, R.ReportType, R.DoctorFileName, R.PracticeFileName, R.LastFailure, R.Results from results as R with(index(online_onlinesent_ndx)) inner join Practice as P on R.PracticeCode = P.PracticeCode where ((R.Online = 1) and (R.OnlineSent = 0)) Order by R.PracticeCode, R.Stored The query returns a simple result set of records. I was just questioning the wisdom of the suggestion of making code changes (which is just modifying the SQL commands) as apposed to investigating the database which had grown to a size it was never intended to be. Regards Peter Maddin Applications Development Officer PathWest Laboratory Medicine WA Phone : +618 9473 3944 Fax : +618 9473 3982 E-Mail : [email protected] The contents of this e-mail transmission outside of the WAGHS network are intended solely for the named recipient's), may be confidential, and may be privileged or otherwise protected from disclosure in the public interest. The use, reproduction, disclosure or distribution of the contents of this e-mail transmission by any person other than the named recipient(s) is prohibited. If you are not a named recipient please notify the sender immediately. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Greg Low (greglow.com) Sent: Tuesday, 22 June 2010 7:29 AM To: 'ozDotNet'; 'Peter Maddin' Subject: RE: Embedding SQL index hints into SQL commands for SQL SERVER. Hi Peter, The only reason to normally need query hints are for bugs in the SQL optimizer (rare) or a handful of very specific cases. Nothing you have mentioned below sounds like one of those cases. Otherwise, invariably you'll be "de-tuning" the query by telling SQL Server which indexes to use. The problem will relate to the table and/or index design, almost for sure. Fixing the actual problem will leave you with no need to be trying index hints. 99% likely that the existing indexes are not actually useful for the given query, that's why SQL Server is ignoring them. Add a hint is likely to just make the performance worse. Regards, Greg -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Tuesday, 22 June 2010 9:07 AM To: ozDotNet; Peter Maddin Subject: Re: Embedding SQL index hints into SQL commands for SQL SERVER. Hi Peter, Databases that only have a relatively small amount of data and are unusually large are often caused by having transaction logs that have blown out in size. The usual way to reduce the size of the transaction log is simply to back it up properly (depending on the recovery model). As for using index hints, I usually don't need them, because with a simple re-arrangement of the query, it will select the correct index anyway (in most cases). This usually involves reorganising the where clause / joins so the order of columns matches the order of the index. I recently blogged on ways that I was able to successfully improve query performance at one of my clients. May or may not help, I don't know, but it might give you some ideas: http://tonesdotnetblog.wordpress.com/2010/06/19/tips-for-optimizing-sql-serv er-2008-queries/ and I have an older article with different set of tips for 2005 which may be relevant too: http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-s ql-server-2005- queries/ T. On Mon, Jun 21st, 2010 at 9:07 PM, Peter Maddin <[email protected]> wrote: > I have no idea what the DBA has done to determine what the issues are. > He is usually not that informative. > Its just do this. > > I believe the database has grown too large. > The system has been working fine until the database grew to past 15 > GB. > There is no reason for it to be any near this size. > I said the database needs to be purged of unnecessary records and > shrunk however I am a mere developer. > > > Regards Peter > > On 21/06/2010 6:39 PM, Michael Nemtsev wrote: > > > > How did you determine that the issue with the indexes? Did you use > any > > SQL performance monitoring tools that point you to the indexes or > it's > > just a guess? > > > > Hits are usually the last resort when you can't rewrite the query > to > > have the right execution plan, or when you query is quite complex > and > > can't pickup the right indexes, so you point your query to the > right > > stuff. > > > > *Michael Nemtsev* > > > > Microsoft MVP > > > > B: http://msmvps.com/blogs/laflour > > > > S: http://www.sharepoint-sandbox.com > > > > *From:* [email protected] > > [mailto:[email protected]] *On Behalf Of *Maddin, > Peter > > *Sent:* Monday, 21 June 2010 7:57 PM > > *To:* [email protected] > > *Subject:* Embedding SQL index hints into SQL commands for SQL > SERVER. > > > > I have a DBA that is suggesting that to improve performance I need > to > > embed optimizer hints in the SQL commands that I use against the > > database so it will use specific indexes. > > > > Is this a good idea? > > > > I believe performance problems are related to the size of the > database > > as my test database (2 GB) has no problems at all and the > production > > database has grown large and fat with lots of unnecessary records > that > > should have been purged years ago. > > > > I would have thought that embedding optimizer hints into a SQL > > statement in my code increases the level of coupling between my > code > > and the database which is not a good thing. > > > > Also it's the role of the optimizer to determine what indexes > > (provided they exist) to use. > > > > Has anyone else done this sort of thing? > > > > Am I just being stupid (or more so than usual)? > > > > *Regards Peter Maddin* > > *Applications Development Officer* > > *Path**West Laboratory Medicine WA* > > *Phone : +618 9473 3944* > > *Fax : +618 9473 3982* > > *E-Mail : [email protected] > > <mailto:[email protected]>* > > *The contents of this e-mail transmission outside of the WAGHS > network > > are intended solely for the named recipient's), may be > confidential, > > and may be privileged or otherwise protected from disclosure in the > > > public interest. The use, reproduction, disclosure or distribution > of > > the contents of this e-mail transmission by any person other than > the > > named recipient(s) is prohibited. If you are not a named recipient > > > please notify the sender immediately.* > > >
