I have a stored procedure that suddenly started performing horribly.
The query plan didn't look right to me, so I copy/pasted the code and
ran it (it's a single SELECT statement). That ran pretty well and used
a query plan that made sense. Now, I know what you're all thinking...
stored procedures have to optimize for variable parameters, etc.
Here's what I've tried to fix the issue:
1. Recompiled the stored procedure
2. Created a new, but identical stored procedure
3. Created the stored procedure with the RECOMPILE option
4. Created the stored procedure with a hard-coded value instead of
aparameter
5. Changed the stored procedure to use dynamic SQL
In every case, performance did not improve and the query plan remained
the same (I could not easily confirm this with the dynamic SQL
version, but performance was still horrible).
I am currently running UPDATE STATISTICS on all of the involved
tables, but that will take awhile.
I think not. Microsoft says it is possible: one for parallel and one for serial execution. Don't believe that's possible for a stored procedure to change execution plans on the fly. Have an on-going problem with timeout occurring with an application and narrowed the culprit to a stored procedure. I couldn't find any obvious issues database wise, no locks, etc. so I recompiled (altered) the sproc without making any changes and the issue cleared for a couple days.
It happened again to day, and so I recompiled (altered) the sproc and it went away again. No code changes to both application (so they say) and stored procedure. I ran the below code snippet to check for sprocs with multiple cached plans and the offending one came up on a short list. So, my question is, Is it one sproc per query plan or can there be more than one. I understand the connection issues.
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.
I was wondering if anyone had an concrete information about if there is a problem with having too many stored procedures or plans in the cache? Obviously there is an impact on memory but if we can ignore that for the time being, does SQL perform just as well with 100 query plans as it does with 10's millions of plans?
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I'm trying to get an application finished that works like Query Analizer in terms of returning query plans and statistics.
Problem the co-author is having:
>In using ADO to connect to SQL Server, I'm trying to retrieve multiple >datasets AND statistics that are usually returned via the OnInfoMessage >event. For those that are familiar with SQL Server, I need the results >returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options. >Anyone had any luck doing this before?
Can anyone shed any light on this please?
Thanks.
BTW if anyone wants to take a look at the tool so far - to see what I'm delving into: http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1
Hi,We are trying to solve a real puzzle. We have a stored procedure thatexhibits *drastically* different execution times depending on how itsexecuted.When run from QA, it can take as little as 3 seconds. When it iscalled from an Excel vba application, it can take up to 180 seconds.Although, at other times, it can take as little as 20 seconds fromExcel.Here's a little background. The 180 second response time *usually*occurs after a data load into a table that is referenced by the storedprocedure.A check of DBCC show_statistics shows that the statistics DO getupdated after a large amount of data is loaded into the table.*** So, my first question is, does the updated statistics force arecompile of the stored procedure?Next, we checked syscacheobjects to see what was going on with theexecution plan for this stored procedure. What I expected to see wasONE execution plan for the stored procedure.This is not the case at all. What is happening is that TWO separateCOMPILED PLANs are being created, depending on whether the sp is runfrom QA or from Excel.In addition, there are several EXECUTABLE PLANs that correspond to thetwo COMPILED PLANs. Depending on *where* the sp is run, the usecountincreases for the various EXECUTABLE PLANS.To me, this does not make any sense! Why are there *multiple* compileand executable plans for the SAME sp?One theory we have is, that we need to call the sp with the dboqualifier, ie) EXEC dbo.spHas anyone seen this? I just want to get to the bottom of this andfind out why sometimes the query takes 180 seconds and other timesonly takes 3 seconds!!Please help.Thanks much
We know that a query execution plan exists for Stored Procedures in the Procedure Cache.
What about Views? Does a view have a query execution plan? We know that a View is a virtual table and that virtual table is populated when the view is induced but does it have a Query Execution Plan?
I have tried to find this info for Views in BOL but I cannot see it anywher in BOL.
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1 @Franchise ObjectId , @dtmStart DATETIME , @dtmEnd DATETIME AS BEGIN
SET NOCOUNT ON
SELECT p.Product , c.Currency , c.Minor , a.ACDef , e.Event , t.Dec , count(1) "Count" , sum(Amount) "Total" FROM tb_Event t JOIN tb_Prod p ON ( t.ProdId = p.ProdId ) JOIN tb_ACDef a ON ( t.ACDefId = a.ACDefId ) JOIN tb_Curr c ON ( t.CurrId = c.CurrId ) JOIN tb_Event e ON ( t.EventId = e.EventId ) JOIN tb_Setl s ON ( s.BUId = t.BUId and s.SetlD = t.SetlD ) WHERE Fran = @Franchise AND t.CDate >= @dtmStart AND t.CDate <= @dtmEnd AND s.Status = 1 GROUP BY p.Product , c.Currency , c.Minor , a.ACDef , e.Event , t.Dec
Any way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them. Also any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?
exec sp_executesql N'select cast(5 as int) as DisplaySequence, mt.Description + '' '' + ct.Description as Source, c.FirstName + '' '' + c.LastName as Name, cus.CustomerNumber Code, c.companyname as "Company Name", a.Address1, a.Address2,
[code]....
In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect.the server is using an old cached query plan, but don’t know for sure.
way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them.
Also do you know of any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?
exec sp_executesql N'select cast(5 as int) as DisplaySequence, mt.Description + '' '' + ct.Description as Source,
[Code].....
In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect. I’m thinking the server is using an old cached query plan, but don’t know for sure.
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN
if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
hey.. say i have a table with 4 columns, Id, col1, col2 & col 3the way it works is the id and one of the col's will have info, the other 2 cols will be emptyim trying to write a proc that returns the value of the column that has info + a number that is stored in a string to represent the column eg: CREATE PROCEDURE proc_Test @Id int, @Answer varchar(100) outputasset nocount on select @Answer = col1 +', 1'from myTablewhere Id= @Idif @@rowcount < 1select @Answer = col2 +', 2'from myTablewhere Id= @Idif @@rowcount < 1
select @Answer = col3 +', 3' from myTable where Id= @IdreturnGO but for some reason, it only process's the first select statment and if nothing is in the column, it returns - ', 1' cheers!!!
Hi,I am weak in writing stored procedure and want to learn it step by step.Now I have written a query and the requirement is such that I need to convert it in stored procedure.query:Select distinct empskill.tcatid,Coalesce(prm,0) as prm,coalesce(secn,0) as secn,a as technology,coalesce(skd,0) as skd,coalesce(knd,0) as knd,coalesce(tnd,0)as tnd,coalesce(dnd,0) as dnd from empskill RIGHT OUTER JOIN (select tcatid,Count(skilltypeid) AS prm from empskill where skilltypeid=1 group by tcatid) prms ON empskill.tcatid=prms.tcatid LEFT OUTER JOIN (select tcatid,Count(skilltypeid) AS secn from empskill where skilltypeid=2 group by tcatid) secs on empskill.tcatid=secs.tcatid RIGHT OUTER JOIN (select technology.category as a,empskill.tcatid from empskill,technology where empskill.tcatid=technology.tcatid group by empskill.tcatid,technology.category ) s ON empskill.tcatid=s.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS skd from empskill where skilllevelid=1 group by tcatid) skds on empskill.tcatid=skds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS knd from empskill where skilllevelid=2 group by tcatid) knds on empskill.tcatid=knds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS tnd from empskill where skilllevelid=3 group by tcatid) tnds on empskill.tcatid=tnds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS dnd from empskill where skilllevelid=4 group by tcatid) dnds on empskill.tcatid=dnds.tcatid union select top 1 500 as tcatid,'' as prm,'' as sec,'more' as technology,'' as skd,'' as knd,'' as tnd,'' as dnd from empskill Can you please explain step by step how to convert this to stored procedure.Thanks a lot
In access, I can create a function that I can call in a query. It runs that function iteratively. Do I have the same ability with SQL server? Can I call a stored procedure WITHIN a query? I would like to be able to do something like:
Insert into tblOrders(spFirst_Name) Select {Call spUpperLower(tblClients.First_Name)} from tblClients Where ...
So basically, spUpperLower would run on every First Name Row in the result set.
I want to run a stored procedure from a query... can I do this? Thanks!
CREATE VIEW dbo.V_EmploymentTerminationsResignations AS --the next 3 lines produce errors... declare @StartDate datetime declare @EndDate datetime exec [hrs2].[dbo].[sp_getquarterinfo] --@StartDate OUTPUT, @EndDate OUTPUT --they're designed to replace the hard coded stuff in the WHERE statement...
--a snippet of the originl view... SELECT TOP 100 PERCENT dbo.Employee.OrgID, dbo.Employee.SSN, dbo.Employee.EMPLOYEE_NAME, , dbo.Employee.SPECIAL_STATUS, WHERE ... (dbo.Employee.LAST_PERS_ACTN_DATE BETWEEN CONVERT(DATETIME, '2001-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2001-10-01 00:00:00', 102))
set @CollectionID = (SELECT CollectionID from ProgramOffers where OfferID='@packageID');
WITH CollectionFull (SubCollectionID) AS ( -- Create the anchor query. This establishes the starting -- point SELECT SubCollectionID from v_CollectToSubCollect a where a.SubCollectionID=@CollectionID UNION ALL -- Create the recursive query. This query will be executed -- until it returns no more rows select a.SubCollectionID from v_CollectToSubCollect a inner join CollectionFull b on b.SubCollectionID=a.ParentCollectionID ) Insert Into @Collections SELECT * FROM CollectionFull
select a.RuleName AS MACHINENAME, c.IPAddress0 as IPADDRESS from v_CollectionRuleDirect a inner join @Collections b on b.CollectionID=a.CollectionID inner join v_GS_DEVICE_NETWORK c on c.ResourceID=a.ResourceID where RuleName not in (select distinct a.MachineName as MACHINENAME from v_StatusMessage a, v_StatMsgAttributes b, v_CollectionRuleDirect c, v_GS_DEVICE_NETWORK d, v_Collection e where (a.RecordID=b.RecordID AND a.MachineName=c.RuleName and c.ResourceID=d.ResourceID and c.CollectionID=e.CollectionID) AND b.AttributeValue IN ('@packageID'))
I have a situation where Im calling a stored procedure to insert some information.
When this information is inserted it is given a date/time stamp - say something in Aug.
There will be some corresponding records from the previous month already in the database that have some additional information that was manually added.
I need to query the corresponding records from the previous month and insert that info into the records that were just inserted.
the problem Im having is that i need to grab the most recent corresponding record. could be a day or a month prior to the one I just inserted. the also could be a record for months prior to that.
So how do i get the most recent corresponding record to my inserted record
Any suggestion on how to query this? and then pass the result to an udpate statement
Is there any way to do the following?select Max(FieldOne) From (spGetSomeData 100,'test' )Or do I need to define a view and have the stored proc use that view?The stored proc does some things with temp tables that would bedifficult to replicate with a view which is why I'm asking.
I did a join on two tables to get the following results. I saved theresults in a #temptable.idtable2idtable2descripdateinserted================================================== ==13descrip111/3/200224descrip211/2/200233descrip111/4/200143descrip110/5/200354descrip212/8/200165descrip39/10/2002I want to query that #temptable to get the max date for each table2idand only return those record. So I need a query to get the followresults...idtable2idtable2descripdateinserted================================================== ==24descrip211/2/200243descrip110/5/200365descrip39/10/2002Question...What query can I make with #temptable to give me the results?
I need some help with the following query:DECLARE @SRV VARCHAR(20), @date smalldatetimeSET @SRV = (select @@servername)SET @date = '20040901'select Srv_Name = @SRV, DB_Name = 'DB_NAME', Table_Name ='Info_Table', Date_of_Records = @date,count(*) AS 'Actual Total' ,max (SER_NO)- min (SER_NO)+1 AS 'Desired total ',count(*) - (max (SER_NO)- min (SER_NO)+1) AS 'Missing Records',min (SER_NO) AS 'MIN SER_NO',max (SER_NO) AS 'MAX SER_NO'from Info_Tablewhere DateTime >= @date and DateTime < dateadd(DAY, 1, @date)I would like to get records of next 30 days from the @date. I can copypaste this query 30 times with different date and get the desiredresults but would prefer to use one query only.If possible would like a add a variable to get the table name by usingthe following query into the above query:SELECT DISTINCT so.nameFROM sysobjects AS soINNER JOIN syscolumns AS scON so.id=sc.idWHERE so.name like 'm_%' AND sc.name = 'DateTime' OR sc.name ='SER_NO'So basic idea is to run one simple (or complexed) query to get 30 daysdata of many tables select by the above query.Can someone help please?
Hello All I need help on a query or stored procedure in my C# app what i need to do is make a report to our fisheries service every three months the totals of each species of fish every fishermen catchs. eg Fishermen1 Species1 100Kg Species2 200Kg and so on I can get the results using a select query one at a time using parameters FishermenId,SpeciesId and StartDate and EndDate but with over a 100 species it will take forever to do is there a way i can fill a dataset with the results using the FishermensID and start and end date as the parameters and get the totals for all the species that fishermen has court.I have not got reporting services i want to show the results in a datagridview and i can print it out from there. My tables are Fishermen Table1 FishermenID int FishermenName vchar Details Table2 DetailsID int FishermenID int SpeciesID int SpeciesName vchar Quantity nchar Date smalldatetime Species Table3 SpeciesID int SpeciesName vchar SpeciesCode vchar Hope someone can help Thanks Barry
is it better to use stored procedures or queries in terms of performance? I'm running application in ASP.NET, now the amount of data in the database is not very high, but I expect it'll grow, so I wonder about speed of queries etc.
Is it better to use SELECT ... FROM ... or to prepare stored procedure for such select? What about insert/update/delete?
Try this script to see what queries are taking over a second.To get some real output, you need a long-running query. Here's one(estimated to take over an hour):PRINT GETDATE()select count_big(*)from sys.objects s1, sys.objects s2, sys.objects s3,sys.objects s4, sys.objects s5PRINT GETDATE()Output is:session_id elapsed task_alloc task_dealloc runningSqlText FullSqlTextquery_plan51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,sys.objects s3, sys.objects s4, sys.objects s5 SQL PlanClicking on SQL opens the full SQL batch as a .txt file, including the PRINTstatementsClicking on Plan allows you to see the .sqlplan file in MSSMS========Title: Using a VB Script to show long-running queries, complete with queryplans.Today (July 14th), I found a query running for hours on a development box.Rather than kill it, I decided to use this opportunity to develop a scriptto show long-running queries, so I could see what was going on. (ReferenceRoy Carlson's article for the idea.)This script generates a web page which shows long-running queries with thecurrently-executing SQL command, full SQL text, and .sqlplan files. The fullSQL query text and the sqlplan file are output to files in your tempdirectory. If you have SQL Management Studio installed on the localcomputer, you should be able to open the .sqlplan to see the query plan ofthe whole batch for any statement.'LongestRunningQueries.vbs'By Aaron W. West, 7/14/2006'Idea from:'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp'Reference: Troubleshooting Performance Problems in SQL Server 2005'http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspxSub Main()Const MinimumMilliseconds = 1000Dim srvnameIf WScript.Arguments.count 0 Thensrvname = WScript.Arguments(0)Elsesrvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)If srvname = "" ThenMsgBox("Cancelled")Exit SubEnd IfEnd IfConst adOpenStatic = 3Const adLockOptimistic = 3Dim i' making the connection to your sql server' change yourservername to match your serverSet conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")' this is using the trusted connection if you use sql logins' add username and password, but I would then encrypt this' using Windows Script Encoderconn.Open "Provider=SQLOLEDB;Data Source=" & _srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;"' The query goes heresql = "select " & vbCrLf & _" t1.session_id, " & vbCrLf & _" t2.total_elapsed_time AS elapsed, " & vbCrLf & _" -- t1.request_id, " & vbCrLf & _" t1.task_alloc, " & vbCrLf & _" t1.task_dealloc, " & vbCrLf & _" -- t2.sql_handle, " & vbCrLf & _" -- t2.statement_start_offset, " & vbCrLf & _" -- t2.statement_end_offset, " & vbCrLf & _" -- t2.plan_handle," & vbCrLf & _" substring(sql.text, statement_start_offset/2, " & vbCrLf & _" CASE WHEN statement_end_offset<1 THEN 8000 " & vbCrLf & _" ELSE (statement_end_offset-statement_start_offset)/2 " & vbCrLf & _" END) AS runningSqlText," & vbCrLf & _" sql.text as FullSqlText," & vbCrLf & _" p.query_plan " & vbCrLf & _"from (Select session_id, " & vbCrLf & _" request_id, " & vbCrLf & _" sum(internal_objects_alloc_page_count) as task_alloc, " &vbCrLf & _" sum (internal_objects_dealloc_page_count) as task_dealloc " &vbCrLf & _" from sys.dm_db_task_space_usage " & vbCrLf & _" group by session_id, request_id) as t1, " & vbCrLf & _" sys.dm_exec_requests as t2 " & vbCrLf & _"cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql " & vbCrLf & _"cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p " & vbCrLf & _"where t1.session_id = t2.session_id and " & vbCrLf & _" (t1.request_id = t2.request_id) " & vbCrLf & _" AND total_elapsed_time " & MinimumMilliseconds & vbCrLf & _"order by t1.task_alloc DESC"rs.Open sql, conn, adOpenStatic, adLockOptimistic'rs.MoveFirstpg = "<html><head><title>Top consuming queries</title></head>" & vbCrLfpg = pg & "<table border=1>" & vbCrLfIf Not rs.EOF Thenpg = pg & "<tr>"For Each col In rs.Fieldspg = pg & "<th>" & col.Name & "</th>"c = c + 1Nextpg = pg & "</tr>"Elsepg = pg & "Query returned no results"End Ifcols = cdim filenamedim WshShellset WshShell = WScript.CreateObject("WScript.Shell")Set WshSysEnv = WshShell.Environment("PROCESS")temp = WshShell.ExpandEnvironmentStrings(WshSysEnv("TEMP")) & ""filename = temp & filenameDim fso, fSet fso = CreateObject("Scripting.FileSystemObject")i = 0Dim cDo Until rs.EOFi = i + 1pg = pg & "<tr>"For c = 0 to cols-3pg = pg & "<td>" & RTrim(rs(c)) & "</td>"Next'Output FullSQL and Plan Text to files, provide links to themfilename = "topplan-sql" & i & ".txt"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-2)f.Closepg = pg & "<td><a href=""" & filename & """>SQL</a>"filename = "topplan" & i & ".sqlplan"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-1)f.Closepg = pg & "<td><a href=""" & filename & """>Plan</a>"'We could open them immediately, eg:'WshShell.run temp & filenamers.MoveNextpg = pg & "</tr>"Looppg = pg & "</table>"filename = temp & "topplans.htm"Set f = fso.CreateTextFile(filename, True, True)f.Write pgf.CloseDim oIESET oIE = CreateObject("InternetExplorer.Application")oIE.Visible = TrueoIE.Navigate(filename)'Alternate method:'WshShell.run filename' cleaning uprs.Closeconn.CloseSet WshShell = NothingSet oIE = NothingSet f = NothingEnd SubMain
I have created a Stored Procedure:SELECT ID, Productname, Price, Desc, img_urlFROM ProductsWHERE (ID = [ -- Products.aspx?ID=x -- ])Question: I want to view the product details for that ID in QueryString on my ASP.Net page www.myhomepage.com/Product.aspx?ID=2. How do I?...Using:FormView1SqlDataSourceVB/ASP.Net 2005 & SQL Server 2005 Express.
Hello, my table is clustered according to the date. Has anyone found an efficient way to page through 16 million rows of data? The query that I have takes waaaay too long. It is really fast when I page through information at the beginning but when someone tries to access the 9,000th page sometimes it has a timeout error. I am using sql server 2005 let me know if you have any ideas. Thanks I am also thinking about switch datavase software to something that can handle that many rows. Let me know if you have a suggestion on a particular software that can handle paging through 16 million rows of data.
Hi, I need to create a stored procedure, which needs to accept the column name and table name as input parameter, and form the select query at the run time with the given column name and table name.. my procedure is, CREATE PROC spTest @myColumn varchar(100) , @myTable varchar(100) AS SELECT @myColumn FROM @myTable GO This one showing me the error, stating that myTable is not declared.. .............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters.. Plese help me?? Is it possible in stored procedure..
Hi i am trying to make the "userName" section of the code below dynamic as well, how can i do this, the reason being userName will not always be passed through to it.
ALTER PROCEDURE [dbo].[stream_UserFind] ( @userName varchar(100), @subCategoryID INT, @regionID INT )ASdeclare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like ' + char(39) + '%' + @UserName + '%' + char(39) if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = ' + cast( @subCategoryID as varchar(10))if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = ' + cast( @regionID as varchar(10)) print @StaticStr exec(@StaticStr) )
