|
|
You're in the
sql server programming
group:Difference in query execution from QA and application
sql server programming:
SQL Profiler can help you confirm that you really are issuing the same commands in both scenarios. Are you using the same credentials (username+password or windows account) to access SQL Server in both cases? Are both scenarios executing in the same transaction isolation level? When the query times out what's it doing - working hard or idling? Good luck, Rhys On Sep 20, 9:48 pm, "Arjan de Haan \(ha\)" <2x nospam nospam adwhaan [quoted text, click to view] AT hotpop.com> wrote: > Hi. > > Not sure if this is the proper newsgroup, but here it goes... > > We have a view (large, accessing lots of tables) which for some reason is running fine > when called from the Query Analyzer - returning within a couple of seconds - but times out > when executed from our application. It is exactly the same query, including parameters. > Included in the query is a date parameter. When it is set to yesterday it returns quickly > with the data. If the date parameter is set to today, it takes longer. But when set to > tomorrow, it times out (reproducible). This all happens from the application. Using > exactly the same SQL and run from the Query Analyzer, they all return quickly. > Also, when reindexing the indexes on the table the query runs properly from within the > application too - although not as fast as when executed from within the Query Analyzer. > > Our application uses ADO/OLE DB (via TCP/IP) to connect to the SQL Server. How does Query > Analyzer connect to the server? And is there a way to determine why QA executes the same > queries fast, and our application times out on them? > > Tx in advance.... > ...Arjan...
Arjan de Haan (ha) (2x nospam nospam adwhaan AT hotpop.com) writes:
[quoted text, click to view] > Not sure if this is the proper newsgroup, but here it goes... I would say it is. [quoted text, click to view] > We have a view (large, accessing lots of tables) which for some reason > is running fine when called from the Query Analyzer - returning within a > couple of seconds - but times out when executed from our application. It > is exactly the same query, including parameters. Included in the query > is a date parameter. When it is set to yesterday it returns quickly with > the data. If the date parameter is set to today, it takes longer. But > when set to tomorrow, it times out (reproducible). This all happens from > the application. Using exactly the same SQL and run from the Query > Analyzer, they all return quickly. Also, when reindexing the indexes on > the table the query runs properly from within the application too - > although not as fast as when executed from within the Query Analyzer. Is the view indexed? Or is there an indexed view in the mix? Or is there any indexed computed column? If there is, there is a very important difference between QA and an ADO application. By default QA issues SET ARITHABORT ON when it connects, but an ADO application does not. On SQL 2000, ARITHABORT must be on for an indexed view or an index on a computed column to be considered. (This does not apply to SQL 2005.) There are more settings that have to be set for indexed views and indexed computed columns: ANSI_NULLS, QUOTED_IDENTIFIER, CONCACT_NULL_YIELDS_NULL, ANSI_WARNINGS and ANSI_PADDING. And NUMERIC_ROUNDABORT must be off. But all these are normally set correctly by default. ARITHABORT is the troublesome one. Even if you don't use indexed views or indexed computed columns, ARITHABORT still matters so far that it is an attribute of the query plan, so the application and QA will use different cached entries. The differences in plans would then simply be due to parameter sniffing. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Hi.
Not sure if this is the proper newsgroup, but here it goes... We have a view (large, accessing lots of tables) which for some reason is running fine when called from the Query Analyzer - returning within a couple of seconds - but times out when executed from our application. It is exactly the same query, including parameters. Included in the query is a date parameter. When it is set to yesterday it returns quickly with the data. If the date parameter is set to today, it takes longer. But when set to tomorrow, it times out (reproducible). This all happens from the application. Using exactly the same SQL and run from the Query Analyzer, they all return quickly. Also, when reindexing the indexes on the table the query runs properly from within the application too - although not as fast as when executed from within the Query Analyzer. Our application uses ADO/OLE DB (via TCP/IP) to connect to the SQL Server. How does Query Analyzer connect to the server? And is there a way to determine why QA executes the same queries fast, and our application times out on them? Tx in advance.... ....Arjan...
A good start is to use Profiler to see what is actually sent to the server from each client. (QA
uses ODBC...) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Arjan de Haan (ha)" <2x nospam nospam adwhaan AT hotpop.com> wrote in message
news:eq%23Ote8%23HHA.1208@TK2MSFTNGP05.phx.gbl... > Hi. > > Not sure if this is the proper newsgroup, but here it goes... > > We have a view (large, accessing lots of tables) which for some reason is running fine when called > from the Query Analyzer - returning within a couple of seconds - but times out when executed from > our application. It is exactly the same query, including parameters. > Included in the query is a date parameter. When it is set to yesterday it returns quickly with the > data. If the date parameter is set to today, it takes longer. But when set to tomorrow, it times > out (reproducible). This all happens from the application. Using exactly the same SQL and run from > the Query Analyzer, they all return quickly. > Also, when reindexing the indexes on the table the query runs properly from within the application > too - although not as fast as when executed from within the Query Analyzer. > > Our application uses ADO/OLE DB (via TCP/IP) to connect to the SQL Server. How does Query Analyzer > connect to the server? And is there a way to determine why QA executes the same queries fast, and > our application times out on them? > > Tx in advance.... > ...Arjan... > >
Rhys, [quoted text, click to view] > Just throwing some ideas into the pot; > SQL Profiler can help you confirm that you really are issuing the > same commands in both scenarios. I'm sure we are issueing the same commands. Our app is using a SQL Generator to build the queries, and I can capture the Generator output. I simply copied the generated SQL into the Query Analyzer and noticed the difference. [quoted text, click to view] > Are you using the same credentials (username+password or windows > account) to access SQL Server in both cases? Come to think of it, I think we did not think of that. I'll give it a try. [quoted text, click to view] > Are both scenarios executing in the same transaction isolation > level? We're using an ADO connection, and the isolation level is set to Read Committed. But we didn't set that when executing from the Query Analyzer. I will give it a retry.... [quoted text, click to view] > When the query times out what's it doing - working hard or Hard to tell, since this happens in a production environment at a customer. Backups > idling? > restored on a testserver at our office don't have this particular problem. I'll see what I can find out. Tx for the help. ....Arjan...
Erland,
[quoted text, click to view] > > Is the view indexed? Or is there an indexed view in the mix? Or is there > any indexed computed column? > No, not any of them. This is a simple (well, actually rather complex) view referencing tables and 1 other view. Not really all that fancy... Just checked, but it can't be an indexed view since it uses a subselect query. [quoted text, click to view] > > Even if you don't use indexed views or indexed computed columns, > ARITHABORT still matters so far that it is an attribute of the query > plan, so the application and QA will use different cached entries. > The differences in plans would then simply be due to parameter sniffing. > When executing from the Query Analyzer we didn't include the ARITHABORT attribute. I will retry it along with the other hints from Rhys. Thanks for the pointers so far.... One point to note is that one of the parameters in the query is a datetime field. After issueing a DBCC DBREINDEX on an index which contained that datetime field as its first index field, the query executed normal, even from our application. ....Arjan... ....Arjan...
If running DBREINDEX helps, do you have a maintenance job on the
customer server to run updatestats? In my mind this shouldn't make a huge difference if auto-maintained stats is on but I've seen it work before. Rhys On Sep 21, 8:19 am, "Arjan de Haan \(office\)" <2x nospam nospam [quoted text, click to view] adwhaan AT hotpop.com> wrote: > Erland, > > > > > Is the view indexed? Or is there an indexed view in the mix? Or is there > > any indexed computed column? > > No, not any of them. This is a simple (well, actually rather complex) view referencing > tables and 1 other view. Not really all that fancy... > Just checked, but it can't be an indexed view since it uses a subselect query. > > > > > Even if you don't use indexed views or indexed computed columns, > > ARITHABORT still matters so far that it is an attribute of the query > > plan, so the application and QA will use different cached entries. > > The differences in plans would then simply be due to parameter sniffing. > > When executing from the Query Analyzer we didn't include the ARITHABORT attribute. I will > retry it along with the other hints from Rhys. Thanks for the pointers so far.... > > One point to note is that one of the parameters in the query is a datetime field. After > issueing a DBCC DBREINDEX on an index which contained that datetime field as its first > index field, the query executed normal, even from our application. > > ...Arjan... > > ...Arjan...
[quoted text, click to view]
> I'm sure we are issueing the same commands. Our app is using a SQL Generator to build the queries, > and I can capture the Generator output. I simply copied the generated SQL into the Query Analyzer > and noticed the difference. The API used by the application often do things you don't know about. This is why we suggested to use Profiler so you see the truth. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Arjan de Haan (office)" <2x nospam nospam adwhaan AT hotpop.com> wrote in message
news:eriMSpB$HHA.5948@TK2MSFTNGP04.phx.gbl... > > Rhys, > > >> Just throwing some ideas into the pot; >> SQL Profiler can help you confirm that you really are issuing the >> same commands in both scenarios. > > I'm sure we are issueing the same commands. Our app is using a SQL Generator to build the queries, > and I can capture the Generator output. I simply copied the generated SQL into the Query Analyzer > and noticed the difference. > >> Are you using the same credentials (username+password or windows >> account) to access SQL Server in both cases? > > Come to think of it, I think we did not think of that. I'll give it a try. > >> Are both scenarios executing in the same transaction isolation >> level? > > We're using an ADO connection, and the isolation level is set to Read Committed. But we didn't set > that when executing from the Query Analyzer. I will give it a retry.... > >> When the query times out what's it doing - working hard or >> idling? >> > Hard to tell, since this happens in a production environment at a customer. Backups restored on a > testserver at our office don't have this particular problem. I'll see what I can find out. > > Tx for the help. > ...Arjan... > >
Arjan de Haan (office) (2x nospam nospam adwhaan AT hotpop.com) writes:
[quoted text, click to view] > No, not any of them. This is a simple (well, actually rather complex) > view referencing tables and 1 other view. Not really all that fancy... > Just checked, but it can't be an indexed view since it uses a subselect > query. There could still be an indexed view or an indexed computed column somewhere within the view. [quoted text, click to view] > One point to note is that one of the parameters in the query is a > datetime field. After issueing a DBCC DBREINDEX on an index which > contained that datetime field as its first index field, the query > executed normal, even from our application. Without know the details of your application and how it issues the queries, I don't want to make further speculations. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
"RMJCS" <rhys@rmjcs.com> schreef in bericht news:1190378613.401004.93260@n39g2000hsh.googlegroups.com... [quoted text, click to view] > If running DBREINDEX helps, do you have a maintenance job on the > customer server to run updatestats? In my mind this shouldn't make a > huge difference if auto-maintained stats is on but I've seen it work > before. It _has_ helped before, only we did it by hand then (dropping and recreating the index took care of the problem). Now we have a maintenance job running :-) ....Arjan...
Arjan de Haan (ha) (2x nospam nospam adwhaan AT hotpop.com) writes:
[quoted text, click to view] > Well, since I'm the former head DBA I'm pretty sure I didn't use indexed > views nor indexed computed columns, but then again...is there a simple > way to determine if a view uses any of those underneath, apart from > dissecting each and every view used? From the query plan for example? Yes, examining the query plan you get in QA would tell if this could be the issue. Not that the plan would flag indexed view or indxed computed columns in any particular way, but you have to recognize the view or the column and know that it's a view or computed. [quoted text, click to view] > Not sure what you need to know, but the application (written in Delphi) > executes the queries using an ADO connection. The queries are generated > by our own SQL Generator. I can safely state that it generates the same > query every time for this particular form, apart from the where clause > which is based on the parameters the user enters. One of those > parameters is on the datetime field mentioned. Well, I don't know if the parameters as passed such or are sent inline, and I don't know what conditions you use. In fact, I don't know much at all, and thus the answers you get will be generic only. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
"Erland Sommarskog" <esquel@sommarskog.se> schreef in bericht news:Xns99B353B04DB3Yazorman@127.0.0.1... [quoted text, click to view] > > There could still be an indexed view or an indexed computed column > somewhere within the view. Well, since I'm the former head DBA I'm pretty sure I didn't use indexed views nor indexed computed columns, but then again...is there a simple way to determine if a view uses any of those underneath, apart from dissecting each and every view used? From the query plan for example? [quoted text, click to view] > >> One point to note is that one of the parameters in the query is a >> datetime field. After issueing a DBCC DBREINDEX on an index which >> contained that datetime field as its first index field, the query >> executed normal, even from our application. > > Without know the details of your application and how it issues the > queries, I don't want to make further speculations. > Not sure what you need to know, but the application (written in Delphi) executes the queries using an ADO connection. The queries are generated by our own SQL Generator. I can safely state that it generates the same query every time for this particular form, apart from the where clause which is based on the parameters the user enters. One of those parameters is on the datetime field mentioned. ....Arjan...
I have this problem too!
ASP 3.0 OLE SQL 2000 SP4 IIS6 (2k3 SP2) Using stored procedures and executing them via ADO Command object. I was profiling one of my databases yesterday looking for procedures to tune to increase performance... general maintenance. I saw one sproc call come through that took ~ 11 seconds to run (duration), but the CPU time was only ~ 2.5 seconds. This often means it's got an IO bottleneck and is joining tables inefficiently. I took this query to QA and ran it, duration ~ 3 seconds. I started playing with the connection properties in QA and once I set ANSI_PADDING off it took 11 seconds in QA and closely matched the execution from ASP. So, now I am thinking I am going to change some connection properties in ASP, but am unsure of the best way to do that. Only way I know is to execute them on the Connection before executing my Command... but that would essentially be two round trips to the server. Is there a better way to deal with connection properties through ADO?
I should have also clarified... I know I can add SET ANSI_PADDING ON to
the top of my sproc, but I don't want to do that because I'm afraid it will force a recompile of the procedure...
SQL Guy (sql@devdex.com) writes:
[quoted text, click to view] > I have this problem too! > > ASP 3.0 > OLE > SQL 2000 SP4 > IIS6 (2k3 SP2) > > Using stored procedures and executing them via ADO Command object. > > I was profiling one of my databases yesterday looking for procedures to > tune to increase performance... general maintenance. I saw one sproc > call come through that took ~ 11 seconds to run (duration), but the CPU > time was only ~ 2.5 seconds. This often means it's got an IO bottleneck > and is joining tables inefficiently. I took this query to QA and ran > it, duration ~ 3 seconds. > > I started playing with the connection properties in QA and once I set > ANSI_PADDING off it took 11 seconds in QA and closely matched the > execution from ASP. I don't know what exactly what is going, but ANSI_PADDING is on by default when you connect from ADO. But there is a connection-string property to turn it off. However, to make matters more confusing, the setting of ANSI_PADDING is also saved on column level. That is, if you have ANSI_PADDING off when you create a table, the varchar columns will not have ANSI_PADDING. This can lead to suprises with indexed views and indexed computed columns, but they appear fairly directly, since you cannot create the view or index if a column has the wrong setting. You can see information about the plans in master..syscacheobjects, and there is a column setopts that reflects the settings associated with the cache entry, but unfortunately I don't know which bit is which setting. (In SQL 2005, this is a lot easier, as there is a dedicated DMV for this.) It could be pure chance that you get the same plan in QA with ANSI_PADDING off, or ANSI_PADDING could really have something to do with it. Keep in mind that many times you get different plans due to different settings, the settings themselves does not anything to do with it, but the real issue is parameter sniffing. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
"SQL Guy" <sql@devdex.com> schreef in bericht news:%23RDsyaeAIHA.5184@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] >I have this problem too! ....> [quoted text, click to view] > I was profiling one of my databases yesterday looking for procedures to > tune to increase performance... general maintenance. I saw one sproc > call come through that took ~ 11 seconds to run (duration), but the CPU > time was only ~ 2.5 seconds. This often means it's got an IO bottleneck > and is joining tables inefficiently. I took this query to QA and ran > it, duration ~ 3 seconds. > > I started playing with the connection properties in QA and once I set > ANSI_PADDING off it took 11 seconds in QA and closely matched the > execution from ASP. Hm. Since we generate the SQL code ourselves, I might as well add the ANSI_PADDING to it too if needed. Unfortunately, since the problems in my original post came from a production environment, we had to run the maintenance scripts. Couldn't let our client have these performance problems until we're done testing! So now I'm waiting for the performance problem(s) to return. In the past this has taken about 2 weeks to one month, but now, with the maintenance scripts running I expect it to be much longer. In the mean time I will do some testing on our local server test bed, to see if I can reproduce similar problems as you described. This also gives me an opportunity to see what additional commands QA sends, in comparison to ADO. I will let you know.... ....Arjan...
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |