Urgent: Trace Errors - Data base tuning before final!

Reply

dimitrisv Posted: Sun, Aug 6 2006 5:34 PM

rated by 0 users

I am sorry that I was too busy to write this post earlier. But better late than never.

As the release of 2.1 nears I would urge the development team to use the Profiler and the Data base Tuning Advisor of SQL 2005 before the release.

You may see a brief presentation of the tools in my blog 

I run a trace and analysis of my web server and here are some of the errors that I got in the reports.

E000 exec dbo.cs_user_Get @UserName=N'jded',@UserID=0,@IsOnline=0,@LastAction=N'',@SettingsID=1000   122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @ThreadID=5969,@UserID=28236,@SettingsID=1000,@IsTracked=@p4 output
select @p4   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @p4 bit
set @p4=0
exec dbo.cs_thread_IsTracked @ThreadID=414,@UserID=1001,@SettingsID=1000,@IsTracked=@p4 output
select @p4   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @SettingsID=1000,@PostID=44641   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'. E000 declare @p4 bit
set @p4=0
exec dbo.cs_thread_IsTracked @ThreadID=346,@UserID=1001,@SettingsID=1000,@IsTracked=@p4 output
select @p4   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec GetUserInfoByUserName @username='savexe'   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'GetUserInfoByUserName'.
E000 exec dbo.cs_Roles_Get @UserID=24444,@SettingsID=1000   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Roles_Get'.
E000 exec dbo.cs_Page_Visits_Add @IP=N'75.6.233.54',@VisitCount=6   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Page_Visits_Add'.
E000 exec dbo.cs_LinkCategories_Get @SectionID=496,@SettingsID=1000,@PreLoadLinks=1   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_LinkCategories_Get'.
E000 exec dbo.cs_Roles_Get @UserID=39367,@SettingsID=1000   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Roles_Get'.
E000 exec dbo.cs_User_Avatar @UserID=14200,@SettingsID=1000   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_User_Avatar'.
E000 exec dbo.cs_Page_Visits_Add @IP=N'202.108.22.143',@VisitCount=13   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Page_Visits_Add'.
E000 exec dbo.cs_Roles_Get @UserID=43469,@SettingsID=1000   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Roles_Get'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @SettingsID=1000,@PostID=68093   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.
E000 exec dbo.cs_Threads_GetThreadSet @SectionID=0,@PageIndex=0,@PageSize=20,@sqlCount=N'SELECT count(T.ThreadID)  FROM dbo.cs_Threads T , dbo.cs_PrivateMessages P  WHERE  T.SettingsID = 1000  AND T.SectionID = 0 AND P.UserID = 41785 AND P.ThreadID = T.ThreadID  AND ThreadDate >= ''2006-08-06T18:28:02''  AND IsApproved = 1 AND (Select Convert(bit,Count(*)) FROM dbo.cs_vw_HasReadForum where ((ThreadID is null and MarkReadAfter > T.ThreadID and LastActivity >= T.ThreadDate) or (MarkReadAfter is null and ThreadID = T.ThreadID)) and UserID = 41785 and SectionID = T.SectionID) = 0 ',@sqlPopulate=N'SELECT T.ThreadID  FROM dbo.cs_Threads T , dbo.cs_PrivateMessages P  WHERE  T.SettingsID = 1000  AND T.SectionID = 0 AND P.UserID = 41785 AND P.ThreadID = T.ThreadID  AND ThreadDate >= ''2006-08-06T18:28:02''  AND IsApproved = 1 AND (Select Convert(bit,Count(*)) FROM dbo.cs_vw_HasReadForum where ((ThreadID is null and MarkReadAfter > T.ThreadID and LastActivity >= T.ThreadDate) or (MarkReadAfter is null and ThreadID = T.ThreadID)) and UserID = 41785 and SectionID = T.SectionID) = 0  ORDER BY ThreadDate DESC',@UserID=41785,@ReturnRecordCount=1,@SettingsID=1000   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Threads_GetThreadSet'.

E000 declare @p4 bit
set @p4=0
exec dbo.cs_thread_IsTracked @ThreadID=3802,@UserID=1001,@SettingsID=1000,@IsTracked=@p4 output
select @p4   5 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
S009 exec dbo.aspnet_UsersInRoles_GetRolesForUser @ApplicationName=N'dev',@UserName=N'dragonas81'   26 Event references objects that are not selected to tune

The million dollar question here is this: since these stored procedure, do exist and since most of the time the forum behaves fine, why on earth we do get so many errors of this kind? 
Any comment will be greatly appreciated.
There are many more errors, some of them repeat themselves hundrends of times in the couple of hours that I took the trace and most of them are ommited even in the cs event log. But then again I do get this error as well

E000 exec dbo.cs_Exceptions_Log @SettingsID=1000,@ExceptionHash='1320592453',@Category=999,@Exception=N'System.IO.FileNotFoundException: D:\Internet_Sites\WWW\Forum\forums\2\74662\ShowPost.aspx
   at System.Web.UI.TemplateParser.GetParserCacheItem()
   at System.Web.UI.TemplateControlParser.CompileAndGetParserCacheItem(String virtualPath, String inputFile, HttpContext context)
   at System.Web.UI.TemplateControlParser.GetCompiledInstance(String virtualPath, String inputFile, HttpContext context)
   at System.Web.UI.PageParser.GetCompiledPageInstanceInternal(String virtualPath, String inputFile, HttpContext context)
   at System.Web.UI.PageHandlerFactory.GetHandler(HttpContext context, String requestType, String url, String path)
   at System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, String path, String pathTranslated, Boolean useAppConfig)',@ExceptionMessage=N'D:\Internet_Sites\WWW\Forum\forums\2\74662\ShowPost.aspx',@UserAgent=N'Baiduspider+(+http://www.baidu.com/search/spider.htm)',@IPAddress='202.108.23.76',@HttpReferrer=N'',@HttpVerb=N'GET',@PathAndQuery=N'/forums/2/74662/ShowPost.aspx'   1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Exceptions_Log'.

[:'(]
 

Reply

dimitrisv replied on Tue, Aug 15 2006 6:55 PM

rated by 0 users

Following Bob Beauchemins ( http://www.sqlskills.com/blogs/bobb/ ) help, realized that these errors are due to the fact that the trace-profiler uses the SQL Native Client as opposed to ourown ADO.NET

I think it will be wise to incorporate this client in the projects as well.

Advice can be found here.

http://blogs.msdn.com/dataaccess/archive/2005/07/29/445147.aspx