Does anyone know what's going on, luckily the two failures have been on non critical procedures, but I'm now becoming worried about the stability of the system If anyone else has had a similar issue, please let me know. After a few hours playing with timeouts etc, I remembered the previous problem, and recompiled the stored procedure. Hacks fix it temporarily Of the many areas of SQL Server where performance problems rear their head, few are handled as poorly as parameter sniffing. Playing around with the TSQL appears to fix it temporarily. The same stored procedure worked fine using management studio. A stored procedure uses extensive branching logic. This morning I had a similar problem on another stored procedure, the client executed the stored procedure and timed out before the dataset was returned. As I had done many other things, I wasn't 100% sure what had fixed it, so I didn't worry about it again. After many hours of trying different things, I recompiled this particular stored procedure (re ran the ALTER without changing the code), and the client code started working again exactly as it always had done, no code in either the client or database was changed. I tested the exact same stored procedure using SQL management studio, and the data was returned in about 4 seconds (much less than the configured time out). Only this one stored procedure failed, the rest of the system worked fine. This has worked for may months, but this time, this particular stored procedure returned a timeout error. This recompiling can improve access speed to RC database when user runs the system for the first time after upgrading.Hi all I have had 2 occurrances of a particular problem, can anyone explain why? The first problem was a few months ago where my client code executed a stored procedure, the c# code takes the form of mDT = mTA.GetDate(), where mTA is my table adapter, and mDT is the returned dataset via an XSD layer. This script is to create stored procedure, which is used to recompile all changes in RC database (tables, stored procedures, etc.). SELECT 8 - Descend through the database list 7 - Execute the final string to complete the backups SELECT = 'EXEC sp_recompile ' + '' + char(13) SELECT = TableName, = OwnerName FROM WHERE UIDTableList = 6 - String together the final backup command SELECT = MAX(UIDTableList) FROM 4 - While condition for looping through the database records 3 - Determine the highest UIDDatabaseList to loop through the records FROM sys.objects o INNER JOIN sys.schemas u ON o.schema_id = u.schema_id WHERE o.Type in ('U','P') ORDER BY o. 2 - Outer loop for populating the database names 1 - Declaration statements for all variablesĭECLARE table (UIDTableList int IDENTITY (1,1), OwnerName varchar(128), TableName varchar(128), ObjType varchar(128)) When the RC 3.8 is installed and you have configured the application and upgraded the database (using RC Configuration Tool), run the following script in SQL server: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND TYPE IN (N'P', N'PC'))ĭROP PROCEDURE. After the re-compiling is completed, the issue is gone. This make SQL server take long time to re-compile those changes (maybe a few minutes). In RC 3.8, a SQL schema have been updated (stored procedures and tables) in comparison with RC 3.7 and 3.7.10. When logging in as a Service Desk Agent, you might see nothing displayed in Tasks screen:
0 Comments
Leave a Reply. |