Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Permissions To See Execution Plans


Hi Gurus,

What permissio0ns one should have to view execution plans on SQL SERVER 2005.


Thanks,
ServerTeam


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Execution Plans &<&> Proportionate Execution Times
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.

View Replies !   View Related
Execution Plans
HiCan you give me sone pointers to where I can get more information aboutthe various operations like index seeks,Bookmark Lookups,ClusteredIndex Scan in an execution plan.ThanksRagu

View Replies !   View Related
Execution Plans
I have two schematically identical databases on the same MS SQL 2000 server. The differences in the data are very slight. Here is my problem: the identical query has totally different execution plans on the different databases. One is (in my opinion) correct, the other causes the query to take 60 times as long. This is not an exaggeration, on the quick DB the query takes 3 seconds, on the other DB it takes 3 minutes. I have tried the following to help the optimizer pick a better execution plan on the slow db:

rebuild the indexes
dbcc indexdefrag
update statistics

I CAN put in a hint to cause the query to execute faster, but my employer now knows about the problem and he (and I) want to know WHY this is happening.

Any ideas would be greatly appreciated.

Thanks.

-Scott

View Replies !   View Related
SQL Server Execution Plans
I'm looking for assistance on a problem with SQL Server. We have adatabase where a particular query returns about 3000 rows. This querytakes about 2 minutes on most machines, which is fine in thissituation. But on another machine (just one machine), it can run forover 30 minutes and not return. I ran it in Query Analyzer and it wasreturning about 70 rows every 45-90 seconds, which is completelyunacceptable.(I'm a developer, not a DBA, so bear with me here.)I ran an estimated execution plan for this database on each machine,and the "good" one contains lots of parallelism stuff, in particularthe third box in from the left. The "bad" one contains a "Nested Loop"at that position, and NO parallelism.We don't know exactly when this started happening, but we DO know thatsome security updates have been installed on this machine (it's at theclient location), and also SP1 for Office 2003.So it looks like parallelism has been turned off by one of these fixes.Where do we look for how to turn it back on? This is on SQL Server2000 SP3.Thanks for any help you might have for me!Christine Wolak -- SPL WorldGroup --Join Bytes!

View Replies !   View Related
Same Query - Different Execution Plans??
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

View Replies !   View Related
Saving Execution Plans?
How can I save a graphical execution plan from Query Analyzer. I want to be able to email to someone else to evaluate.

I can save the text execution plans, but these don't contain the level of detail that the graphical ones do.

I can take screenshots but they don't capture the mouse over info and this requires a special screenshot tool as the plan is larger than the scrollable view.

Any ideas?

View Replies !   View Related
Estimated Execution Plans
I have a query that displays different execution plans on my production and development servers. If I look at the "estimated execution plan" through QA the same SQL statement is doing a multiprocessor access in development and single threaded processing in production.

Also the statement in production is spending 17% on a hash statement which it estimates will execute 4+10 whereas in development at the same junction it's not using any resources.

Where can I look to determine why the same statement is producing such significantly differing execution plans? What factors can contribute to this?

View Replies !   View Related
Printing Execution Plans
Does anyone have suggestions on how to print the QA Execution plan so that it is readable. If the query is anything more than a simple select statement, it gets shrunk down so small that it can't be read. It doesn't appear that you can cut and paste into another document. I just can't help but feel that I am missing something obvious...

Thanks,
Mike Sinnott

View Replies !   View Related
Parameter &&amp; Execution Plans.
Hi all,

I have a table TableA with few million rows. When I query TableA , the execution plans changes based on the input parameter as shown below . Why this happens ? How to resolve this ? Any inputs would be appreciated.


SELECT * FROM TableA WHERE Column1 = 1 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 2 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 3 => SELECT -> Parallelism (3%) -> Clustered Index Scan (97%)

SELECT * FROM TableA WHERE Column1 = 4 => SELECT -> Nested Loops -> Index Seek (50%) -> Clustered Index Seek (50%)
(takes a very long time to retrieve the records)

Thanks in advance,

DBLearner.

View Replies !   View Related
Execution Plans - SQLCE 3
Does SQLCE 3 cache execution plans? Or even make use of them?

Thanks

Tryst

View Replies !   View Related
IO Stats And Execution Plans (text)
Hello,
As a requirement, I have to send the DBA's IO Stats and Execution Plans (in text format) to them; with 2005, is there a more automated way?  Before, I tried using a script generator, but it really never did the trick.  Is there a better way of automating?
Thanks.

View Replies !   View Related
Different Execution Plans - Same Data, Same Server
Hi there - hoping someone can help me here!I have a database that has been underperforming on a number of queriesrecently - in a test environment they take only a few seconds, but onthe live data they take up to a minute or so to run. This is using thesame data.Every evening a copy of the live data is copied to a backup 'snapshot'database on the same server and also, on this copy the queries onlytake a second or so to run. (This is testing through the QueryAnalyser)I've studied the execution plans for the same query on the snapshot dband the live db and they seem to be significantly different - why isthis? it's looking at the same data and exactly the same code!!Anybody got any ideas???

View Replies !   View Related
How To Interprete Execution Plans For Queries
Pls tell me where i will be able to find a good material on interpreting the Execution plans................how do i compare 2 diff plans for Quries written in 2 diff ways...giving same output

View Replies !   View Related
SQL Server: Execution Plans + Statistics
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?

Thanks in advance.

View Replies !   View Related
Execution Plans Inconsistent With Performance
I've been working with SQL Server 2005 for a while now and I've noticed some odd behavior that I want to bounce of other members of the community. I should preface that I've been a forum viewer (and occasional contributer) here at SQL Team for a while and I've naturally developed a keen sense for optimizations.

Fundamentally, longer stored procedures with perfectly fine/optimized execution plans are inconsistent with real world performance. In some of these cases, a low subtree cost on a 4 core machine with 16gb of ram and 2 15 drive SAS arrays with little load takes excessively long to run or in some cases doesn't complete.

This isn't due to blocking or resource bottlenecks as I'm quite familiar with built in tools to troubleshoot and resolve those issues. In all cases, I am able to rearchitect the stored procedure into a higher subtree cost variant and get reasonable performance, but it's frustrating to have to redo work and there seems to be no common theme other than longer multi-statement procedures.

I've used SQL Server 2000 extensively and did not notice this level of inconsistency in performance with that product version. Just wondering if others in the community have experiences similar or if I'm just crazy.

Thanks for reading my rant.

- Shane

View Replies !   View Related
Bad Execution Plans In SQL Server 2005
Runnign Sql server 2005 standard edition SP2 on Win 2003 server x86_64, 8GB of RAM - 6.5 for the SQL server 2005
Compare to SQL server 2000 running on the identical box, 2005 has bad plans that are not influenced by rebuilding of the undexes and update statistics. Same data, same application/query, same indexes and tables.
All approrpiate indexes are on place and have updated stats. When running the Index Advisor, there are no recomendations for new indexes.
The main problem is that 2005 makes FTS on places that 2000 does not. The same queries that show FTS on 2005 are much slower then on 2000 and the users are complaining. I thought that if all indexes are good and there are good stats, I would eliminate the problem. But it doesn't look so.
Why SQL 2005 do not use perfectly fine indexes? If I try to put a hint, the situation gets even worse...

I need some advice on how to handle this issue.
Thanska lot, mj

View Replies !   View Related
Invalid Execution Plans SQL Server 2000
All,

I have a situation where there appears to be an invalid execution plan in cache for a stored procedure during busy periods. Estimated rows should equal actual rows approximately.

According to the BOL the plan can become bad because of the following reasons:

Certain changes in a database can cause an execution plan to be either inefficient or invalid, given the new state of the database. SQL Server detects the changes that invalidate an execution plan, and marks the plan as invalid. A new plan must then be recompiled for the next connection that executes the query. The conditions that cause a plan to be invalidated include:

Any structural changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).


New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.


Dropping an index used by the execution plan.


An explicit call to sp_recompile.


Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).


For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.

In our case none of the above is happening. The plan just for some reason becomes invalid. Microsoft is looking into this but I was hopeing someone on this board would have experienced the same type if issue.

Our work around so far has been to issue DBCC FREEPROCCACHE when the cpu starts to go up(Supposedly because of these bad plans).

This is a really high volume environment and a stored procedure may be hit in the 100,000s of times. For example at the peak today one was hit over 900,000 times.

If you have any ideas or experience with this please post here or email me directly.

Thanks

Bill


Here are the details.

Bad plan/cardinality run during high CPU utilization
----------------------------------------------------
Rows EstimateRows StmtText
------ --------------- ---------------------------------------------
17 670.735 select distinct userid,[level],username from
17 670.735 |--Sort(DISTINCT ORDER BY:([Union1009] ASC, [
42 670.871 |--Concatenation
40 19.6799 |--Nested Loops(Inner Join, OUTER REFERENCES:
40 19.6799 | |--Filter(WHERE:([dvr].[OrderDept]=[dvr]
45 19.6799 | | |--Bookmark Lookup(BOOKMARK:([Bmk10
45 19.6799 | | |--Nested Loops(Inner Join, OU
13 13.9279 | | |--Clustered Index Seek(O
45 1.41299 | | |--Index Seek(OBJECT:([rl
40 1 | |--Nested Loops(Inner Join, OUTER REFERE
40 1 | |--Compute Scalar(DEFINE:([Expr1016
40 1 | | |--Constant Scan
40 1 | |--Clustered Index Seek(OBJECT:([rl
2 651.191 |--Hash Match(Inner Join, HASH:([ddd].[OrderD
66 71.9 |--Clustered Index Seek(OBJECT:([rl_util].[db
2 807.639 |--Compute Scalar(DEFINE:([Expr1012]=Convert(
2 807.639 |--Filter(WHERE:([did].[CountryCode]=[@countr
2 807.639 |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJE
2 807.639 |--Nested Loops(Inner Join, OUTER REFERENCES:
2 1026.26 |--Nested Loops(Inner Join, OUTER REFERENCES:
2 13.9279 | |--Clustered Index Seek(OBJECT:([appl_se
2 73.6838 | |--Index Seek(OBJECT:([appl_security_dat
2 1 |--Index Seek(OBJECT:([appl_data].[dbo].[div0

Baseline plan taken under normal operating conditions
-----------------------------------------------------
Rows EstimateRows StmtText
------ -------------- ---------------------------------------------
17 16.656 select distinct userid,[level],username
17 16.656 |--Sort(ORDER BY:([Union1009] ASC))
17 16.656 |--Hash Match(Union)
45 11.2222 |--Hash Match(Inner Join, HASH:([dvr].[orderd
45 22.5168 | |--Hash Match(Inner Join, HASH:([va].[ve
10 14.606 | | |--Clustered Index Seek(OBJECT:([ap
18787 6429.8 | | |--Filter(WHERE:([dvr].[orderdept]=
20143 20281.9 | | |--Index Seek(OBJECT:([rl_util
80 83.8833 | |--Compute Scalar(DEFINE:([Expr1012]=Con
80 83.8833 | |--Clustered Index Seek(OBJECT:([rl
1 5.44687 |--Sort(DISTINCT ORDER BY:([ddd].[acctdeptnbr
2 5.44687 |--Nested Loops(Inner Join)
2 7.98062 |--Filter(WHERE:([did].[countrycode]=[@countr
2 7.98062 | |--Bookmark Lookup(BOOKMARK:([Bmk1006]),
2 7.98062 | |--Nested Loops(Inner Join)
2 8.03501 | |--Nested Loops(Inner Join)
2 1 | | |--Clustered Index Seek(O
2 8.03501 | | |--Index Seek(OBJECT:([ap
2 1 | |--Index Seek(OBJECT:([appl_da
2 1 |--Filter(WHERE:([ddd].[orderdeptnbr]=[ddd].[
2 1 |--Clustered Index Seek(OBJECT:([rl_util].[db

View Replies !   View Related
How To Reduce Number Of Query Execution Plans...
Hi everyone,

I've executed this script to navigate through a set of records based on the min value of recordid, and perform individual column updates on a user table which is only about 550 rows in size but I'd still like to know how I can reduce the number of execution plans to just one for the query analyzer?
So far, I get (1 row(s) affected) for every row that is updated and takes over 2 1/2 minutes to update this small table. The slowdown may be partially caused by the excessive paging that's going on my machine (we're trying to work that issue out).
Can anyone make a suggestion on optimizing this query?
thanks,
Irene
DECLARE @lvID int, @lvFullName VARCHAR(50)

SET @lvId = (SELECT MIN(rowid) FROM irene)
SELECT @LVID
--SET @lvFullName = (SELECT fullname FROM irene WHERE rowid = @lvId)

WHILE @lvId IS NOT NULL
BEGIN
UPDATE irene
Set FirstName = Ltrim(Substring (@lvFullName, charindex(',', @lvFullName)+ 2, Len(@lvFullName))),
LastName = Ltrim(Substring (@lvFullName, 1, charindex(',',@lvFullName)-1))
Where rowid = @lvId
SET @lvId = (SELECT MIN(rowid)
FROM irene
WHERE rowid > @lvId)
SET @lvFullName = (SELECT FullName
FROM Irene
WHERE rowid = @lvId)
END

View Replies !   View Related
SQL Server 2000 Query Analyser Execution Plans
I have two sql statements in query analyser.

1)

SELECT * FROM Member WHERE MemCode = 'ABCDEF'

2)

SELECT TOP 100 Percent FROM Member WHERE MemCode = 'ABCDEF'

The first statement users an Index Scan to find the data whilst the second one uses an index seek, which by definition is a lot faster than the first. The performance gains (I/O cost) is 10 to 50 times better. I haven't been able to find anything to support it. Has anyone come across this and more importantly is anyone aware of any problems using this method. e.g. table locking issues etc. And if not then why don't Microsoft use this as a default method for simple select statements??

View Replies !   View Related
Trigger Execution Permissions
Probably a simple question.

Do triggers execute using the permissions of the user that caused the trigger to execute?


Thanks!

View Replies !   View Related
DTEXEC &&amp; Package Execution Permissions
Using System.Diagnostics.Process, I am executing DTEXEC within a WCF Service hosted in IIS. The service is configured with a fixed identityvia anapplication pool, so the context in which the process executes is that of the fixed identity.

When DTEXEC gets executed, unless the fixed identity executing it is a member of local admin group, I get a message in the console that states "Connect to SSIS Service on machine <machine name> failed: Access is Denied.

I haven't had any luck without adding the fixed identity to the local Admin group and this is not the best option for obvious reasons.

What group membership/permissions must this fixed identity be tied to on the SSIS server in order to succesfuly execute the package?

Note: I am familiar with proxy credentials, and understand that SQL Server Agent job is the best way to execute a package remotely but the Database Engine is not a system requirement for our product, so these are not an option.

Thanks in advance,

Rick

View Replies !   View Related
SSRS Permissions Error: Report Execution
I am trying remove a report's execution schedule and I get this error in the report manager website:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help Only members of sysadmin role are allowed to update or delete jobs owned by a different login.


This is the error I get in SSMS:
------------------------------

An internal error occurred on the report server. See the error log for more details. (rsInternalError) (Report Services SOAP Proxy Source)

For help, click: http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3042.00

------------------------------
ADDITIONAL INFORMATION:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) (ReportingServicesLibrary)

For help, click: http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3042.00

------------------------------

Only members of sysadmin role are allowed to update or delete jobs owned by a different login. () (.Net SqlClient Data Provider)

------------------------------
BUTTONS:

OK
------------------------------


However I AM the systems administrator.

This is a dev server and I restored a backup from production to this server so I think that may be part of the issue.

However, I am unable to locate any details on which user account it thinks lacks these permissions. I am logged in as the administrator local to the machine and sys admin of the SQL database server.

Suggestions?

EDIT: Could it be an issue with the Network Service account? I have that listed as the account in the IIS app pool that report server is using.

Since that was setup on my production server then moved to my dev server, could that be an issue as it is a LOCAL account not a domain account?

Maybe it doesn't have proper access?

View Replies !   View Related
Left Join Vs Left Outer Join Syntax Generates Different Execution Plans


Anyone know whyusing

SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of

SELECT *
FROM a LEFT JOIN b
ON a.id = b.id

generates a different execution plan?

My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".

Anyenlightenment is very appreciated.

Thanks


View Replies !   View Related
Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp
after moving offVS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected?Is the fact that debugger doesntshow this behavior also expected?

View Replies !   View Related
Execution Procedure Stored During Execution Of The Report .


Hello :

How to execute a procedure stored during execution of the report, that is before the poster the data.

Thnak you.

View Replies !   View Related
Table Permissions Versus View Permissions
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.

View Replies !   View Related
Execution In Scheduled Job Vs Direct Execution
Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table

If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.

I used the same record, same parameters, and the same statements to call the stored procedure.

Any idea?

View Replies !   View Related
User Permissions Vs. Role Permissions
Which takes precedence?

User Tom has Exec permissions on MyStoredProc. He is also a member of the Public role which does not specifically state that he has Exec permission on MyStoredProc. However it also does not implicitly 'deny' that permission. Is there a "cumulative permission" effect here? Or is the Public role being 'more' restrictive and therefore not allowing Tom to Exec MyStoredProc?


From BOL.
"A user account can be a member of any number of roles within the same database. For example, a SQL Server user can be a member of the admin role and the users role for the same database, with each role granting different permissions. The effective permissions on an object granted to a member of more than one role are the cumulative permissions of the roles, although a denied permission in one role has precedence over the same permission granted in another role. For example, the admin role may grant access to a table, whereas the users role denies access to the same table. A member of both roles is denied access to the table because denied access is the most restrictive."


If I grant Exec permission on the MyStoredProc to the Public role, then Tom can execute the procedure.

I am all confused.

Help,
Troy

View Replies !   View Related
Different Query Plans
I have 2 SQL databases which are the same and are giving me differentquery plans.select s.* from hlresults hinner join specimens s on s.specimen_tk = h.specimen_tkwhere s.site_tk = 9 and s.location in ('ABC','WIAD')and s.date_collected between '2/1/2003' and '2/3/2006'order by s.location, s.date_collectedBoth boxes have the same configuration, the only difference is that oneof them is a cluster.The Acluster box is taking twice as long to run the query.I have run statistics on both, and the cluster is still creating abitmap and running some parallelism which the other box is not.Also, the the first step, the A1 box estimates the rows returned to bearound 80K and the actual rows returned is about 40K - subtree cost =248. The Acluster box estimates 400K - subtree cost=533!After running statistics, how can it be so off?I've also reindexed to no avail . . .any insight would be very much appreciated. We just moved to this newsystem and I hate that the db is now slower -A1:affinity mask -2147483648 2147483647 0 0allow updates 0 1 0 0awe enabled 0 1 1 1c2 audit mode 0 1 0 0cost threshold for parallelism 0 32767 0 0Cross DB Ownership Chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1default full-text language 0 2147483647 1033 1033default language 0 9999 0 0fill factor (%) 0 100 90 90index create memory (KB) 704 2147483647 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 32 4 4max server memory (MB) 4 2147483647 14336 14336max text repl size (B) 0 2147483647 65536 65536max worker threads 32 32767 255 255media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 4096 4096nested triggers 0 1 0 0network packet size (B) 512 32767 4096 4096open objects 0 2147483647 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote login timeout (s) 0 2147483647 0 0remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 0 0scan for startup procs 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0Acluster:affinity mask -2147483648 2147483647 0 0allow updates 0 1 0 0awe enabled 0 1 1 1c2 audit mode 0 1 0 0cost threshold for parallelism 0 32767 0 0Cross DB Ownership Chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1default full-text language 0 2147483647 1033 1033default language 0 9999 0 0fill factor (%) 0 100 90 90index create memory (KB) 704 2147483647 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 32 4 4max server memory (MB) 4 2147483647 14336 14336max text repl size (B) 0 2147483647 65536 65536max worker threads 32 32767 255 255media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 4095 4095nested triggers 0 1 0 0network packet size (B) 512 32767 4096 4096open objects 0 2147483647 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote login timeout (s) 0 2147483647 0 0remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 0 0scan for startup procs 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0

View Replies !   View Related
Maintenance Plans
We have Veritas' Backupexec running in our Enterprise and the Veritas Install actually installs MS SQL Server MSDN on each Server in the Enterprise.

It looks like it also sets up a default Maintenance plan within each of the MSDN Instances.

I guess my question is.. Can I manage the Maintenance Plans on these MSDN Instances via the SQL Server EM GUI from my desktop?? Seems like when I look at the Maintenance plans alot of the options are greyed out or not available. What I am trying to do is modify one of the maintenance plans to have the backups deleted after one week (One of the Instances has been running a complete backup on the Backupexec Databases for a year and there are a years worth of backups on the Server) but the option to "remove files older than" is 'greyed out' ??????

View Replies !   View Related
SQL 2K5 Maintenance Plans
Eh uhhhh where did the old '-DelBkUps 7DAYS' (delete files older than 7 Days) option go?

Is this a future product enhancement?

I fee like such a dirty noob.

View Replies !   View Related
SP Query Plans
It is my understanding, based on reading BOL and TechNet that when using a SP a query plan is established the first time a query is run using that SP. Hence if I run a query against a SP that has an index on an id field, and if there is 20,000 records in the table, and I request everything greater than 19,995 The index will be used. I turn right around and run it again and request everything great than 1, it will still use the index because the query plan specifies to use the index, even though in this instance when running it a second time a table scan may be more optimal. However I am finding this is not the case. The optimizer seems to be choosing which index to use, and when not to use the index. This appears to completely contradict all the existing documentation. Has anybody else encounter this same thing? Further research shows that memusage is incrementing the number of query plans and the size of the proc each time it is run. I am NOT using the `With Recompile` option.

View Replies !   View Related
SQL Maintenance Plans
SQL7: I have added a Maintenance Plan to backup to 4mm dat tape the master and msdb SQL databases as well as another database relative to our application called WISE. This works fine; however, it appears to always append to the media as opposed to overwriting (preferred). Any help would be appreciated.....

View Replies !   View Related
Backup Plans
I am new to SQL Server 2000 & need ya all's help!!
I am trying to set up a database maintenance plan to back up databases & transactional logs. If I do a full backup once a week & a transactional backup every day...will I be safe enough to have enough backups to be able to restore to any point of time by restoring the full backup & the transactional logs upto that point?
In other words, I am asking what are the points to consider & what should be a decent backup plan? Do transactional logs take stored procedureal changes also?

Thanks for ur help,
Edward

View Replies !   View Related
Maintenance Plans
Hi,

I am going to set up maintenance plans on all our SQL servers (7.0 and 2000). I have found several 'tutorials' on how to do this, but no one is describing the options in detail. Can you guys/gals please help me out? We have alot of small databases and some medium (1-2GB).

Thanks//Stefan

View Replies !   View Related
Maintenance Plans In SQL 7.0
I have created a maintenance plan to run dbcc on a 8 gig database. Some night the job reports sucessfull with an execution time of 2 seconds. Other nights it reports successfull with an execution time of 6 hours.

Does anybody have any idea of what this is doing. It is impossible for it to run dbcc checkdb in two seconds. Are there bugs with this routie that anyone knows about

View Replies !   View Related
Maintenance Plans.....
Hi All,
For some reason my maintenance plan has a problem
deleteing old database files. I've set it to delete
all backups older then a day, but this might work for
a few days then it will just stop deleteing the files???
Can anyone help?
David.

View Replies !   View Related
Maintenance Plans Over UNC
Does anyone get any issues creating "Backup" jobs as a Maintenance Plan when specifying the backup location as a UNC path (e.g. "\backup_bladeBACKUPS")?

For some reason, if i try using the UNC path for a 1-time backup, it works, but when I am trying to put it into a scheduled job, it does not 'seem' to perform the Backup step.

Help please...
seethem

View Replies !   View Related
Maintenace Plans
Does any body have any good recommendations for maintenace plans?

Here are few questions I have.

When should indexes be re-indexed?
What should be done first? Reorganize indexes or rebuild indexes?
How often backups should be done and what kind?
How often should database statistics be updated?
Do database statistics need need to be updated on system databases?
How often should a database integrity check be done?
How long should history be kept for?
What is a good order for tasks should be done?

Any input would be great

Eytan

View Replies !   View Related
SP2 And Maintenance Plans
I created several Maint.Plans before installing SP2. Now I need to modify them and I get the following error. I cannot even Create new ones, because of the Enumerate error. Please advice if this error is due to the same issues mentioned on this blog.

When replying please cc me at Camilo.Torres@bellsouth.com

Thanks

TITLE: Microsoft SQL Server Management Studio

------------------------------

Enumerate target servers failed for Job 'Daily Maintenance Plan 1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+target+servers+Job&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

View Replies !   View Related
SP2 Maintenance Plans
Does anyone know if Sql Server 2005 Express SP2 support scheduled backups and maintenance plans?

Looking at http://msdn2.microsoft.com/en-us/library/bb283536.aspx , it appears to...

Thanks.

View Replies !   View Related
Database Maintenance Plans
Hello everyone,
I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:

[DBName]Full:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:sqldataMSSQL$P001Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'

[DBName]Maint Integrity Checks:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '

[DBName]Maint Optimizations:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

Any help or insight would be greatly appreciated!
Thanks in advance,
Cat

View Replies !   View Related
Query Plans && Statistics
Gurus,

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


Much Appreciated!!

View Replies !   View Related
Maintenance Plans Fail
I used the wizard to create maintenance plans on my SQL 2000 servers. Part of the plan fails (checking data and index linkeage) when the job runs in off hours. It fails because it says the DB is not in single user mode. Shouldn't something in the plan take care of this. The wizard gives you no options. Or is this a bug. These jobs ran fine in SQL 7

View Replies !   View Related
Database Maintenance Plans
What is the recommended schedule for SQL Server maintenance plans that perform database integrity checks and optimizations?

Also, more specifically, how often should we be re-building our indexes?

Thanks for your help.

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved