SQL Server Performance Forum – Threads Archive
execution plan reuse in SQL Server 2000Have a large web application usin ADO SQL Server 2000. I have been view the syscacheobjects table and I find multiple rows (execution plans) generated when static SQL with numeric constants and string literals are used. If they are replaced with paramater markers the execution plans have usercounts > 1. I thought SQL Server 2000 optimizer was smarter and would normalize the SQL statements to figure out if an already cached execution plan can be used? Are execution plans reused only in parameter markers are used in a SQL statement? If using ADO.net is ADO.net framework using parameter markers when talking to SQL Server 2000. or is this also an ADO feature? My problem is many thousand users using small queries that each cause and execution plan to be generated instead of reused because the app is using static SQL instead of parameter markers? enrique
So are you using ADO to SELECT, INSERT, UPDATE, and DELETE data instead of using stored procedures? If so, have you taken a Profiler trace of the activity to see what kind of code ADO is generating when talking to SQL Server? Sometimes, I have seen ADO produce poorly written Transact-SQL that can’t be easily cached and re-used. If you use stored procedures for all of your database communications, and if you use the object owner name and the object name if your object references (like dbo.table_name), then SQL Server will very efficiently reuse execution plans. Of course, there are some exceptions to this, and they are discussed on my website under the topic of stored procedures.
Brad M. McGehee
Always go with the stored procedure approach with parameters if possible. Much easier to get cached query plans. SQL Server has gotten better at reusing query plans for Ad hoc sql (didn’t do it at all in SQL 6.5) but there are still many scenarios where SQL Server will not be able to reuse a plan for an ad hoc query. If you are using auto-generated select,update,delete,insert commands via the recordset object in ADO (rs.add, rs.update etc.) then as Brad mentions this auto-genreated sql is not often cached. If that’s the case, and you want to keep coding with that approach then going over to ado.net could be an idea. In ado.net you can now specify the specific insert, delete and update commands (in the DataAdapter object) to be used, instead of using auto-generated ones. These commands can be stored procedures. But then again, going over to asp.net might be a bigger step than just changing your current application code from ad hoc queries to queries/stored procedures using parameters. /Argyle
I have found a pattern for static SQL and execution plan cacheing. The optimizer will reuse a plan if the SQL statement has literals that are referenced by a basic data type. If so the optimizer will
"auto-parameterize" this SQL statement and then find a match if this similar SQL statement is executed again. The optimizer consistently
auto-parameterized columns with integer data types. My case is that we autogenerate SQL that has binary data therefore lots of hex strings and SQL optimizer ignores these for auto-parameterizing therefore new execution plans are executed for every SQL statement that is executed. I thought we might be able to do a quick fix by just fully qualifying the database tables but in my investigation found out that parameterizing SQL is the best bet and using stored procedures is one way to do it. Our problem with just using stored procedures solely is we need to do SQL Server and Oracle and the way result sets are passed back to calling program are very different.
Based on your discoveries, I would say to overcome your problem you will have to have 2 different stored procedures for SQL Server and Oracle.
"How do you expect to beat me when I am forever?"
The dilemma of dealing with multiple database backends is always a problem. As royv suggests, the best way, from a performance perspective, is to use different stored procedures. This is more work, but worth it when it comes to performance.
Brad M. McGehee