Explain Plan

Jul 20, 2005

Hello.
Does someone of you know if is there a 'explain plan' like function in SQL
Server (similar to explain plan in Oracle or DB2)
If so then how it works, where stores data and how it can be retrieved?

Best regards
Bagieta
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
dbDeveloper - Multiple databases editor

http://prominentus.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

View 5 Replies


ADVERTISEMENT

Explain Plan

Mar 17, 2008

Dear 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.

View 1 Replies View Related

SQL Statement And Explain Plan Caching.

Oct 24, 2006

I 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.

View 1 Replies View Related

SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache

Mar 14, 2015

Is 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.

View 2 Replies View Related

Cached SQL Plan Vs. Stored Proc Plan

Dec 12, 2002

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


Any info would be appreciated.

thanks

Kevin

View 4 Replies View Related

Can Someone Please Explain This To Me...

Jun 26, 2007

why 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

View 2 Replies View Related

How Do I Run 'explain' On A Sql??

Jun 27, 2001

Hi, does anyone know how to run an 'explain' on a sql query on SQL Server 2000?

Thanks.

View 1 Replies View Related

Could Someone Please Explain The Following...

Dec 1, 2005

As 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

View 10 Replies View Related

Explain

May 2, 2008

Any one explain me about this query exactly wat it will do

backup log iiswebappauditdb with truncate_only

View 3 Replies View Related

Explain Me

May 8, 2008

set @tb1='rptmain.Month01.DBO.LCDB' in

View 1 Replies View Related

Can U Just Explain....

Nov 3, 2007

Query:
insert into aaaa values(',Â?'&',',Â?'&');

BUJJI.

View 6 Replies View Related

Can Some Explain This To Me?

Oct 25, 2007

Hello,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 Related

Please 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); } } }} 

View 1 Replies View Related

Please Explain This SQL Trigger

Sep 25, 2005

I 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 Related

Explain Xp_cmdshell

Feb 2, 2006

I'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 Related

EXPLAIN Function

May 19, 2008

Is 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

View 1 Replies View Related

Can Someone Explain This Behaviour?

Jul 23, 2005

Hello 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 Related

Could Someone Explain This Script To Me

Jan 29, 2008

Hi,

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.

View 14 Replies View Related

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

View 18 Replies View Related

Could Anybody Explain To Me Why Sqldatadpater Does Not Allow Subqueries?

Aug 17, 2007

Dear 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

View 1 Replies View Related

You Can't Explain This! ARRRRG! SQL Ghost!

Dec 23, 2007

Hello 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
 
 
 
 

View 4 Replies View Related

Can Anyone Explain To Me Why This Is Not A Good Idea

Jan 28, 2008

I 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
 
 

View 6 Replies View Related

Explain Exists() Function

Apr 8, 2008

Can any one explain to me how to use the exists() function and what exactly it is used for?

View 1 Replies View Related

Can Someone Explain The Precision Of An Integer In A Sql Db Pls

Nov 3, 2003

Hi 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

View 1 Replies View Related

Mysql EXPLAIN = What In MS SQL 2000

Oct 29, 2004

If 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?

View 1 Replies View Related

Can Someone Explain This Strange Value Of @@ROWCOUNT?

May 31, 2006

I 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?

View 3 Replies View Related

Explain The Below Stored Procedure

May 7, 2008

CREATE 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

View 3 Replies View Related

Can Any One Explain This Query Behavior

Aug 24, 2006

db_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 Related

I Need A CONNECTION GURU To Explain What Is Going On.

May 26, 2006

I 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.

View 4 Replies View Related

Prepared SQL Plan Vs. Procedure Plan

Nov 23, 2005

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

View 1 Replies View Related

Explain To Noob, Database Uploading

Dec 25, 2007

hi,
From my ealier post I learned that a MDF file can be read and written to even if I did not attach the database in the SQL Sever Management Studio.
Is that behavior because of the connection string where is has 
AttachDBFilename=|DataDirectory|DNN12_24_07.mdf  ,
Is that what "attaches" the database?  This runs on SQL Express2005.
Should I change the connection string  to:
 name="SiteSqlServer"  connectionString="Server=(local);Database=ASPNET;uid=;pwd=;" providerName="System.Data.SqlClient" />
?
Is there a statment in the XML of web.config that defines |DataDirectory| of the original connection string  that points to APP_DATA where the mdf file physically resides?
Thanks in advance?
-KK
  

View 1 Replies View Related

Backup Failed Using Tape. Can Someone Explain?

Nov 7, 2001

My company is using backup exec to bacup all servers and when it came to this particular server these backups were skipped. I checked the log files and backups that are ran within SQL. Can someone explain to me what may have caused this. My guess is that there was something with the media that was trying to backup this server. Thanks for all of you help.

Below is the error message that was recieved after backup exec attempted to backup up this server.


Media Name: "DIFF"
Backup of "SQLserver1E$ "
Backup set #49 on storage media #1
Backup set description: "Daily 5"
Backup Type: DIFFERENTIAL - Changed Files
Backup started on 11/05/2001 at 11:46:00 PM.
The item MSSQL7Datadatabase1_Data.MDF in use - skipped.
The item MSSQL7Datadatabase1_Log.LDF in use - skipped.
The item MSSQL7Datadatabase2_Data.MDF in use - skipped.
The item MSSQL7Datadatabase2_Log.LDF in use - skipped.
The item MSSQL7Datamaster.mdf in use - skipped.
The item MSSQL7Datamastlog.ldf in use - skipped.
The item MSSQL7Datamodel.mdf in use - skipped.
The item MSSQL7Datamodellog.ldf in use - skipped.
The item MSSQL7Datamsdbdata.mdf in use - skipped.
The item MSSQL7Datamsdblog.ldf in use - skipped.
The item MSSQL7Dataorthwnd.ldf in use - skipped.
The item MSSQL7Dataorthwnd.mdf in use - skipped.
The item MSSQL7Datapubs.mdf in use - skipped.
The item MSSQL7Datapubs_log.ldf in use - skipped.
The item MSSQL7Datasysdev_Data.MDF in use - skipped.
The item MSSQL7Datasysdev_Log.LDF in use - skipped.
The item MSSQL7DataTEMPDB.MDF in use - skipped.
The item MSSQL7DataTEMPLOG.LDF in use - skipped.
Access denied to directory emp.
^ ^ ^ ^ ^

Backup completed on 11/05/2001 at 11:47:02 PM.
Backed up 55 files in 48 directories.
19 items were skipped.
Processed 84,537,951 bytes in 1 minute and 2 seconds.

View 1 Replies View Related

Pl Explain About Fill Factor In Sql 2005

May 27, 2008

Hi,

Now i am doing a R&D on indexes in SQL 2005. I want to know clearly what is the actual uses of Fill Factor? What is advantage and disadvantage of it?

Where it should use this fill factor?

Please answer me as soon as possible.

Ganesh.

View 17 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved