SQL Statement And Explain Plan Caching.
Oct 24, 2006I know that I can monitor the SP caching by enabling the profiler.
Does any body know how can I check whether the SQL statement or its explain plan is cached or not?
Thanks,
Fargaly.
ADVERTISEMENT
Explain Plan
Mar 17, 2008Dear All,
what exactly is ExplainPlan and maintenance plan? and where would we use these?
i've found some artilce in the NET, but i'm not satisfied
please provide me good articles and your words for me
Vinod
Even you learn 1%, Learn it with 100% confidence.
Explain Plan
Jul 20, 2005Hello.Does someone of you know if is there a 'explain plan' like function in SQLServer (similar to explain plan in Oracle or DB2)If so then how it works, where stores data and how it can be retrieved?Best regardsBagieta=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=dbDeveloper - Multiple databases editorhttp://prominentus.com=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
View 5 Replies View RelatedExecution Plan Caching For Prepared Statements
Nov 22, 2007I think I have a problem with the execution plan caching in context of prepared statements. Please comment and advise.
When caching a new execution plan SQL Server apparently takes into account the actual query parameters and the current situation of the SQL Server (open transactions, transaction locks, current workload and so on). That can cause the same prepared statement to be executed verry badly with other parameters.
I am having trouble with a production system where some queries more or less suddenly start running extremly bad. The reason is an execution plan which might be optimal for some cases but is in general verry bad. Forcing a recompile of execution plans either by updating statistics or running sp_recompile solves the problem for some time. But after an unpredictable time the bad execution plan is comming back. Probably the good execution plan might also be reinstalled after som time but I cannot wait for this to happen.
The factor between good and bad execution plan is about 160 and increasing (30ms vs. 5000ms).
Please comment and advise,
Thanks
Lookup - Full Caching Vs Partial Caching Vs No Caching
Sep 13, 2005I needed to do lookup on tables with approx 1 million records (how else do I know if record already exists?).
View 7 Replies View RelatedSQL Server 2012 :: Sort In Execution Plan - Delete Statement?
Nov 11, 2014I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which increased performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:
ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache
Mar 14, 2015Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
Cached SQL Plan Vs. Stored Proc Plan
Dec 12, 2002We 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
Any info would be appreciated.
thanks
Kevin
Can Someone Please Explain This To Me...
Jun 26, 2007why do the following return the same datasets?
select * from myTable where myData = ''
select * from myTable where myData = ' '
in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL...
P.S. I'm using T-SQL
How Do I Run 'explain' On A Sql??
Jun 27, 2001Hi, does anyone know how to run an 'explain' on a sql query on SQL Server 2000?
Thanks.
Could Someone Please Explain The Following...
Dec 1, 2005As previous threads have indicated I've been trying to figure out what's slowing down our 3rd party ETL tool.
My research has figured out the culprit, but now I need some advice on the best way to handle it.
Here's what's going on...
SPID 1 = dynamic sql passed from Crystal
SPID 2 = ETL tool
SPID 3 = report in stored proc
Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.
Here's the scenario
SPID 1 is running (inefficient select)
SPID 2 is waiting
30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH
SPID 3 fires off
Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1
Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.
My questions:
1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update?
2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?
Thanks in advance
Explain
May 2, 2008Any one explain me about this query exactly wat it will do
backup log iiswebappauditdb with truncate_only
Explain Me
May 8, 2008set @tb1='rptmain.Month01.DBO.LCDB' in
View 1 Replies View RelatedCan U Just Explain....
Nov 3, 2007Query:
insert into aaaa values(',Â?'&',',Â?'&');
BUJJI.
Can Some Explain This To Me?
Oct 25, 2007Hello,I run the following query on a database (SQL 2005):delete from person where pers_companyid in (select pers_companyid fromcompany where comp_expiry is not null)I wanted to delete all people associated with a company which had anexpiry date. The mistake I made was that the column pers_companyiddoes not exist. It should have been comp_companyid.However, the query ran anyway and deleted all records from my persontable? If I run the subquery on its own then it doesn't run as thecolumn is missing.Shouldn't I have got an error running this query?Thanks,David
View 2 Replies View RelatedPlease Explain Transaction For Me
Sep 29, 2007 I found this on http://msdn2.microsoft.com/en-us/library/86773566.aspx which i am hoping may be able to resolve my problem but I am not sure I actually understand it. Where it shows // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction");
is the "SampleTransaction" nothing more then just a name of the transaction that is being created through this code or is it actually pointing to something?I believe that I understand the rest. Oh and in case you have a better way of doing what I am needing here is the SQL Query that is pulling info for me to display on a pageSELECT p.product_ID, p.class_ID, p.category_ID, p.product_name, p.product_desc, p.product_image, p.product_dimension, p.product_o1, p.product_o2, p.product_o3, p.product_ac, p.product_ph, p.product_photo, pcl.class_name, pca.category_nameFROM products AS p INNER JOIN productClass AS pcl ON p.class_ID = pcl.class_ID INNER JOIN productCategories AS pca ON p.category_ID = pca.category_IDBasically I am using the transaction to perform multiple updates seeing as how I will need to update multiple tables. private static void ExecuteSqlTransaction(string connectionString){ using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } }}
Please Explain This SQL Trigger
Sep 25, 2005I am new to triggers.I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @counter = 1 When does this counter change value? USE Northwind GO CREATE TRIGGER PriceCheck ON [Order Details] FOR INSERT AS DECLARE @counter int SET @counter= @@ROWCOUNT DECLARE @unitprice money DECLARE order_details_insert_cursor CURSOR FOR SELECT Unitprice FROM inserted IF @counter = 1 BEGIN IF(SELECT UnitPrice FROM inserted) > 300 -- If the price entered is greater than 300 BEGIN --print a warning PRINT 'Cannot enter price greater than 300' --Take back the command ROLLBACK TRANSACTION END END ELSE BEGIN OPEN order_details_insert_cursor FETCH NEXT FROM order_details_insert_cursor INTO @unitprice WHILE @@FETCH_STATUS = 0 BEGIN if @unitprice > 300 -- If the price entered is greater than 300 BEGIN --print a warning PRINT 'Cannot enter price greater than 300' --Take back the command ROLLBACK TRANSACTION RETURN --Exit trigger immediately END FETCH NEXT FROM order_details_insert_cursor INTO @unitprice END CLOSE order_details_insert_cursor END DEALLOCATE order_details_insert_cursor
View 3 Replies View RelatedExplain Xp_cmdshell
Feb 2, 2006I've got some questions about xp_cmdshell and was hoping someone could explain some things to me. I've written a trigger for a table that uses 'xp_cmdshell' to launch a VB.Net application. The syntax is correct and it "seems" to work but in the way I intended. When the app launches there is no GUI present but when I check the task list I see that the exe has been launched. This intrigued me so I started playing with it in Query Analyzer. When I run the commandexec master.dbo.xp_cmdshell 'C:WINDOWSsystem32otepad.exe'I seen a process for notepad show up on my processes list started by 'system' as opposed to myself and notepad still doesn't open up for me to see. I also noticed that the query executes until I kill that particular process. I'm guessing cause it's waiting on the tranaction to complete. What I want to know though is why can't I see the program when I open like this? Is it because 'system' is the user behind it or is there some extra parameter to throw in the xp_cmdshell?
View 3 Replies View RelatedEXPLAIN Function
May 19, 2008Is there some equivalent to MySQL's EXPLAIN function? (or some utility that does the same thing?)
Basically the function takes a query and will return a list of the tables in the query and the number of rows that would need to be checked. It's used to check how well the indexes are set. (If a table returns a very high number, that could be a bottleneck for the query).
Any help is appreciated. Thanks.
-D. Israel
Can Someone Explain This Behaviour?
Jul 23, 2005Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin
View 8 Replies View RelatedCould Someone Explain This Script To Me
Jan 29, 2008Hi,
Could someone help me understand this script:
DECLARE @TempValue tinyint
SELECT @TempValue = 1
FROM dbo.TABLE_NAME (tablockx updlock holdlock)
WHERE 1 = 2
To my understanding, it would lock all the row in the table at once.
Can Someone Explain This Error Please
Oct 9, 2007
Upon executing the following:
create function test()
returns @tmp table
(
somevalue varchar(max)
)
as
begin
select '' as ttt
into tmp
insert into @tmp(somevalue)
select ttt
from tmp
return
end
I get this error:
Msg 443, Level 16, State 15, Procedure test, Line 8
Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.
I've searched around and honestly cannot find a definitive description of what this means. Can anyone explain in simple terms for a simpleton like me?
Thanks
Jamie
Could Anybody Explain To Me Why Sqldatadpater Does Not Allow Subqueries?
Aug 17, 2007Dear experts,
Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS.
Well, actually i was using IN.
I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed??
Really appreciate it if some expert can tell me.
Thanks in advance
You Can't Explain This! ARRRRG! SQL Ghost!
Dec 23, 2007Hello all,
Where do I start? My son and I wrote a small ASP.net 2.0 website on XP IIS 5. Works ok with the standard login controls.
I FTP'ed the files up to a Windows server2003. I installed the SQL publishing wizard. I created a .sql file of the data base.
I ran the file in SQL2005 manager to build the data base on SQL express running on the Windows 2003 server. It installed, no errors. great!
The website would not run with Data base connection errors , like files or data base was read only, duplicate database , can not run mydatabase.create. something like that.
I created a new app pool and made sure network service was the account. Gave network server permissions to the folders, it started working great.
Now sit down. I created a few accounts using the website ok, no errors, Great! Then check the database to see if the passwords were encrypted. The new accounts were not there.
But the accounts went somewhere???? Must be two databases???? One hidden?
I detached the database I created with the .sql script file.... to see if I would get an error on the browser. nope still worked. Hmmmm other hidden database still working.... I stopped database server
in the management tool, It still worked....Ok, I'll get the house check for poltergiests.
I went to SERVICES. The service was stopped(SQLEXPRESS).. I changed auto start to manual. Browsed back to the site again, finally an error.
I restarted the service, website worked again, but the database, the only user database, is still detached. Created more user accounts with no error....
Seached the hard drives for .MDF files. Found the file I detached and also the ASPNETDB.MDF that I orginally FTP'ed to the server earlier in the APP_DATA folder of the website. I renamed the
MDF file to see if then I would get an error, yep I did. It then automatcly re-created the MDF file exactly the same size. Two files in the directory now and now a new error in the browser
about a database mismatch. Ok that makes sense. But it will not allow me to rename the new file so I can rename the original.
Question is how does SQLEXPRESS run the ASPNETDB.MDF file in the web folder which is not attached to the database engine?
Why use a SQL pub wizard if ASP.NET creates the database automaticly? Or just "Runs" it? AM I HIGH?
Thanks in advance, before I take drugs and sit in a dark closet.
-Wade
Can Anyone Explain To Me Why This Is Not A Good Idea
Jan 28, 2008I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely.
Is a view a good idea in this case? Is the Select * from View really a bad idea?
Thanks
Explain Exists() Function
Apr 8, 2008Can any one explain to me how to use the exists() function and what exactly it is used for?
View 1 Replies View RelatedCan Someone Explain The Precision Of An Integer In A Sql Db Pls
Nov 3, 2003Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???
Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.
Thanks
Mysql EXPLAIN = What In MS SQL 2000
Oct 29, 2004If you have a table named Users then using mysql
you can run EXPLAIN Users; to get all the fields & types, etc.
How would you do this same thing in SQL 2000?
Can Someone Explain This Strange Value Of @@ROWCOUNT?
May 31, 2006I have noticed the following strange situation, but cannot find any explanation. The @rows variable is 1 , even though the @@ROWCOUNT value just after the select statement is 0. (I have checked this by stepping through the SP).
update Products set ParentId = @parent where ParentId = @lastParentIdset @err = @@ERRORset @rows = @@ROWCOUNT
However, if I set @rows before @err then the @rows is correctly set to 0. (as in script below).
update Products set ParentId = @parent where ParentId = @lastParentIdset @rows = @@ROWCOUNTset @err = @@ERROR
Can someone please explain why this is happenning?
Explain The Below Stored Procedure
May 7, 2008CREATE procedure r_routeGetCache1
@pLongest varchar(32),
@pLongestCls int,
@pDate datetime,
@pSrcInt varchar(7),
@pSrcIntGroup varchar(64),
@pSrcIntDom varchar(64),
@routeclass int=0 ,
@pCLI varchar(32)='',
@pOperatorGroup int = 0 ,
@isgrade int=0
as
begin
declare @pc varchar(2)
declare @dd int
declare @hh int
declare @mm int
set @pc=left(@pLongest,2)
set @dd=1+(@@datefirst+datepart(dw,@pDate)-2)%7
set @hh=(datepart(hh, @pDate)-0)
set @mm=datepart(mi, @pDate)
set nocount on
--- Azam
select top 1 @routeclass = routecls from r_interface
where [group] = @pSrcIntGroup
and state='I'
if @routeclass is null set @routeclass=0
--- End
--insert
CREATE TABLE #operator_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[cls] [varchar] (1) NOT NULL ,
[pc] [varchar] (2) NOT NULL
)
insert into #operator_selected(routecls,oprid,cls,pc)
select x.routecls,x.oprid,x.cls,x.pc
from RoutingV3..r20_route_timecls x (nolock),
RoutingV3..r_TimeCls y with (nolock),
RoutingV3..r_interface tit with (nolock),
RoutingV3..r_timecode tco with (nolock),
RoutingV3..r_daycode dco with (nolock)
where x.routecls=0
and tit.id = x.oprid and tit.state = 'I'
and y.tintid =x.oprid and y.cls=x.cls
and y.dc=dco.id and y.tc=tco.id and y.sc=0
and @dd between dco.d1 and dco.d2
and ((24+@hh+isnull(tit.prefixcls,0))%24) * 100 + @mm between tco.h1
and tco.h2-1
and x.pc=@pc
CREATE TABLE #timecls_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[pc] [varchar] (2) NOT NULL ,
[prefixcode] [varchar] (50) NOT NULL ,
cnt int,
clsA int,
clsP int,
clsO int,
clsW int,
[cls] [varchar] (1)
)
insert into
#timecls_selected(routecls,oprid,pc,prefixcode,cnt,clsA,clsP,clsO,clsW,cls)
select a.routecls, a.oprid, a.pc, max(a.prefixCode) prefixCode,
count(*) cnt,
sum(case when a.cls='A' then 1 else 0 end) clsA,
sum(case when a.cls='P' then 1 else 0 end) clsP,
sum(case when a.cls='O' then 1 else 0 end) clsO,
sum(case when a.cls='W' then 1 else 0 end) clsW, 'Z' cls
from RoutingV3..r20_route12 a(nolock), #operator_selected b
where a.routeCls=@routeclass --b.routeCls
and a.oprId=b.oprId and a.cls=b.cls
and a.pc=b.pc
and left(@pLongest,prefixLen)=a.prefixcode
--- Exclude Artificial
--and not (id<0 and parentId is not null and parentId>0)
group by a.routecls, a.oprid, a.pc
update #timecls_selected
set cls = (case when (clsW>0) and (cnt = clsW) then 'W'
when (clsO>0) and (cnt = clsO) then 'O'
when (clsP>0) and (cnt = clsP) then 'P'
when (clsA>0) and (cnt = clsA) then 'A'
else 'Z' end)
update #timecls_selected
set cls = (select max(a.cls) from RoutingV3..r20_route12 a (nolock)
where a.routecls=#timecls_selected.routecls
and a.oprid = #timecls_selected.oprId
and a.pc=#timecls_selected.pc
and a.prefixCode=#timecls_selected.prefixCode
and ((a.cls='A' and #timecls_selected.clsA<>0) or
(a.cls='P' and #timecls_selected.clsP<>0) or
(a.cls='O' and #timecls_selected.clsO<>0) or
(a.cls='W' and #timecls_selected.clsW<>0))
)
where cls = 'Z'
CREATE TABLE #route (
seqno int identity(10,10),
priority int,
[id] int,
isactive int,
reason int,
exception int,
exceptionCls int,
calcexception int,
ext int,
oprid int,
parentid int,
routecls int,
prefixcode varchar(50),
universe varchar(5),
domain varchar(5),
pdomain varchar(5),
[group] varchar(40),
interface varchar(40),
userinfo varchar(40),
hint varchar(100),
clsorg char(1),
cls char(1),
cost float,
flag int,
rating int,
access varchar(10),
redlist int ,quality float)
if @isgrade =0
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else isnull(x.exceptionCls,0) end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc and
x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.cost
end
else
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else 10 end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc
and x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.quality,x.cost
end
set nocount on
select top 5
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #topN
from #route x, RoutingV3..r_interface y with (nolock)
where x.isActive=1
and x.oprId=y.id
and @pLongest like x.prefixcode+'%'
and exceptionCls<>0
order by seqno+[calcException], x.cost
select
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #all
from #route x, RoutingV3..r_interface y with (nolock)
where x.oprId=y.id
and @pLongest like x.prefixcode+'%'
order by seqno+[calcException], x.cost
delete #all
from #topN a, #all b
where a.id = b.id
set nocount off
select * from #topN where isActive=1
union all
select * from #all where isActive=1
end
Can Any One Explain This Query Behavior
Aug 24, 2006db_TBOdb_TBT-------------------------------------------------------------------------------------TBOID | Date TBTID| TBOID| Date-------------------------------------------------------------------------------------rp01 | 01/08/2006 ap01 |rp01|02/08/2006many rows ap02 | rp01 |05/08/2006ap03 |rp03|04/08/2006I want to find TBTTD field of table db_TBTwho have of db_TBO date db_TBT table dateand TBOID should be 'rp01' of both tableswhen I give a query as it works as I neededSelect TBT.Datefrom db_TBO TBO , db_TBT TBTwhere TBT.TBOID = 'rp01'and TBO.TBOID ='rp01'and TBO.Date TBT.DateMy doubt is when I run the following query" Select TBT.Date from db_TBO TBO , db_TBT TBT where TBT.TBOID = 'rp01'"it gives me more than 7 recordswhen I run the query using some change" Select TBT.Date , TBT.TBID from db_TBO TBO , db_TBT TBT whereTBT.TBOID = 'rp01' "it gives more rows than previous rowCan u give me any explations.ThanksPai
View 1 Replies View RelatedI Need A CONNECTION GURU To Explain What Is Going On.
May 26, 2006I have a application that uses a connection that is always active until the application is closed, and many other connections that are opened and closed as different data is requested. I DO close and dispose these connections, datasets, and data readers after requesting data to release the resources. BUT they still show in SQL Server 2005 Express Edition. I believe they are NOT active and are part of the Pooling system.
The problem is the connections don't seem to be reused, the Pooling system just makes more, so I end up with hundreds of them until no more connections can be created but SQL Server; hence timing out on some attempt to open a connection.
I have experimented with the 'Min Pool Size' and 'Max Pool Size' values in the connection string but the only change is behavior is that I can make more connection showing in SQL Server but the problem still happens anyway.
I am rewriting this application from VB6 to VB.Net. The VB6 version uses SQL Server 2005 Edition too and the connection problem doesn't exist.
Any ideas would be appreciated.
Prepared SQL Plan Vs. Procedure Plan
Nov 23, 2005I 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
View 1 Replies View Related