The query plan created by sp_prepare is used during the sp_execute but it's very slow compared to running this query ad hoc or with sp_executesql. If I clear the proc cache before running the sp_execute this runs in less than 1 second. The tables used are pretty large (8 - 15 million rows) but are indexed correctly and I've updated the statistics and rebuilt the indexes but neither improves the performance.
Does anyone know why using the sp_prepare statement causes a poor query plan?
I am looking at the plan caches/cached pages from the perspective of sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) ) from sys.dm_os_memory_cache_counters where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP' a slight change from a query in http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB from sys.dm_os_memory_cache_counters where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Hi, We have an application which fetches data from a table which has approximately 1 million records.
1. Nearly 25 users will be using this application concurrently. 2. frequent updations will be done to the records in the geographyrolecurriculum table. 3. This table has 1 clustered index and 4 nonclustered index bounded to it.
Problem Statement: 1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors. When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly. Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?
2. Will the stored procs timeout if the tempdb is full ?
I have a database X where user A has db_datareader role. User A can select data but cannot update/insert/delete.
Recently user A connected to my database using Brio SQRW tool and could successfully run an update command in database X. As I researched I found out that SQRW uses RPC calls with extended procedures sp_prepare and sp_execute to run an update command.
User A does not have explicit permission to either of these stored procedures. Additionally User A does not have access to master database where there procedures reside.
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€?? Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans. Thx.
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
We have a debate in our team about embedded SQL vs. Stored Procs.
The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?
I can't find any definitive information on pros and cons between the two methods.
If there are no major performance issues, or gotchas, I guess it comes down to developer preference.
SP Pros: - Great SQL support in VS.NET (dev, debug, integration) - Seperation of database specific code from middle tier. - Less lines of code in middle tier - VS.NET support for .xsd dataset definitions. - Logic closer to data for more demanding processes.
Embedded SQL Pros: - Less artifacts for version control - Better encapsulation of logic
I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed to be modified to be called fully qualified (e.g. exec dbo.sp_###instead of exec sp_###), but I don't think that those will increase theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1
On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two hit ratios, and why would we have such a significant difference between the two?
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL, N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT', @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.
Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.
I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.
Snippet that is not working:
declare @db_name varchar(80)
declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.
Hi I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using EXEC sp_execute sqlstmt. Here is piece of code.
DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' OPEN c1 FETCH NEXT FROM C1 INTO @v_TableName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @v_SQL= 'DROP TABLE ' + @v_TableName --EXEC @v_SQL PRINT @v_SQL IF @v_Error<>0 BEGIN SELECT @ErrorCount=@ErrorCount+1 PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName --GOTO ErrorHandler END FETCH NEXT FROM C1 INTO @v_TableName END CLOSE c1 DEALLOCATE C1
hi everybody How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server. ie a string can contain insert into a table statement,delete a row from a table statement, update etc. Thanks all of You
Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.
I'm using sp_ExecuteSQL and have hit the 4000 character wall
Hi all,Can sp_executesql used inside a user defined function, itried but it has compiled well, but when i call the functio it showsOnly functions and extended stored procedures can be executed fromwithin a function.What i have went wrongThanks in advancethomson
I have a full sql statement which was generated dynamicly, and need toexecute that string and then take the output and generate aspreadsheet document based on the output. I'm new to sql and the bookI have doesn't really explain much. Anyone with an example of theirwork would be appreaciated.thank you.
(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @callingQueue + ' INTO @msgTable WHERE conversation_group_id = '
+ CAST(@conversationGroup AS char) + '), TIMEOUT 2000'
EXEC sp_executesql @SQL, N'@msgTable TABLE output', @msgTable out
I get the following message:
Msg 137, Level 15, State 2, Procedure CENTRAL_Queue_Processor, Line 92
Must declare the scalar variable "@msgTable".
I have decalred the variable but it is a table variable, this leadds me to believe sp_execute sql only supports scalar varibles not table variables, BOL does not say yes or no in this respect.
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
Hi all, I just wanted to know why this doesn't work: if @1's values is computer---------------------------------------------------------------------------------------------------------- BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ' + '''%@x1%''' + ' OR notes like ' + '''%@x1%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%@x1%' OR notes like '%@x1%'-----------------------------------------------------------------------------------------------------------But this works: BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ''%''+ @x1 + ''%'' OR notes like ''%'' + @x1 + ''%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%'+ @x1 + '%' OR notes like '%' + @x1 + '%'---------------------------------------------------------------------------------------------------------------I just don't get it ?? Doesn't sp_executesql just replaces the @x1 with @1?
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
Hi, I want to use the output of the sp_executesql to update a coulmn in the table. example -first i run the below to get output execute sp_executesql @Query, @returnedCount output
-then I want to use that output to update another coulmn in the table update tableName set coulmn=@returnedCount
I am new to this and cannot figure out how. Can someone please guide me? thank you!!
Hi all i have Function and in the context of this function i need to build a Dynamic Query String according to input parameters and execute it with sp_executesql. BUT until now i didn't know that SQL doesn't allow to have Exec command within a function,am i right? Apparently this is true because for example create the following Function..
Create Function Test(@Input int) Returns int AS Begin Exec sp_who -- only for Test purpose Return @Input End
Now Execute this --> Select dbo.test(12)..... Sql Server will return the following Error
Server: Msg 557, Level 16, State 2, Procedure Test, Line 6 Only functions and extended stored procedures can be executed from within a function.
Could Any one help me? i need function with dynamic Sql execution because i can only use function in SELECT statements !!!
Any help greatly would be appreciated. Kind Regards.
IF OBJECT_ID(N'aquery') is not null DROP FUNCTION aquery GO
CREATE FUNCTION aquery() Returns nvarchar(500) as Begin Declare @var nvarchar(500); Set @var = 'Select Distinct Description from dbo.tblScanners'; Return @var end GO
exec sp_executesql aquery;
I created that code to prove if it works. But the result doesn´t appear, and the message is:
I have not used this sp. We have a dynamic SQL statement generated by a sp.For performance reasons I would like to use it to reduce the number ofexplain plans created. I would like to understand its usage and pitfalls(if any) to its use. Any comments from the user community?
