Effect Of Do Not Recompute Statistics Option

Jul 13, 2007

I'm looking into the automatic recompilation of stored procedures and
I have been reading up on the "Do not recompute statistics" option on
indexes.

Am I correct in concluding that disabling the "Do not recompute
statistics" option for an index, will ensure that no automatic
recompilations will occur as a result of updates to data in that
index?

Am I also correct in understanding that the "Update Statistics" will
still update statistics for the index even if the "Do not recompute
statistics" option is disabled?


Regards

Bjørn

View 1 Replies


ADVERTISEMENT

Stealth Indexes & Statistics Recompute

Jul 26, 1999

After our upgrade and migration from v6.5 to v7.0, there are new indexes shown on the sysindexes table. All of these new indexes have names that start with the characters '_WA_Sys_'.

Before the upgrade, one database had 88 indexes but now it has 643. One table went from 1 index to 92.

The systems person who did the install and upgrade believes that these new objects only appear to be indexes, and that they are statistics that are automatically generated to aid in performance.

a) Does anyone else have these?
b) What are they?
c) Where can I read about them?
d) Could they cause performance slowdown?

View 1 Replies View Related

Recompute Statistics In Sql*server Via JDBC

Jan 20, 2006

We are inserting huge amount of data (could be several millions) during a fact upload. Therefore after the upload we recomputed the statistics. In oracle this can be done using the following sql statement:

analyze table <table_name> compute statistics

Do you know if there is any equivalent statement in SQL*Server? it has to run via JDBC like a SQL statement.

Thanks.

View 1 Replies View Related

Effect Of Update Statistics On Database And On An ASP Application

Jun 13, 2001

Hi

I had run a stored procedure in my server that update statistics against all user defined tables in my database (MSSQL 7.0).

Since then I am getting errors in my ASP application where I am reffering to adovbs.inc.

Here is an example of errors I get.

Microsoft VBScript runtime error '800a0411'

Name redefined: 'adOpenForwardOnly'

/Essai/adovbs.inc, line 14



Below is the stored procedure I have run against the database.

Can anybody help tank you guys.



CREATE PROCEDURE update_all_stats
AS
/*
This PROCEDURE will run UPDATE STATISTICS against
ALL user-defined tables within this database.
*/
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U'
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)


BEGIN
IF (@@fetch_status <> -2)


BEGIN
SELECT @tablename_header = "Updating " +
RTRIM(UPPER(@tablename))
PRINT @tablename_header
EXEC ("UPDATE STATISTICS " + @tablename )
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES" +
" *************"
PRINT @tablename_header
PRINT " "
PRINT "Statistics have been updated FOR ALL tables."
DEALLOCATE tnames_cursor

View 1 Replies View Related

How To Turn Off STATISTICS XML Option?

May 4, 2008

Hi,

How can I turn off STATISTICS XML option while executing a query in new query window of SQL server Management Studio?

View 1 Replies View Related

Auto Created Statistics And Missing Statistics

Jul 20, 2005

Hello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper

View 5 Replies View Related

Unit Of Time-statistics In Client Statistics

Aug 1, 2006

What is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?

Currently I get mostly 0´s, but if I try and *** up a query on purpose I can get
it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?

I would also like to know if it´s possible to change the precision.


- Nikolaj

View 3 Replies View Related

In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing

Apr 27, 2008

Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio  2005 Setup.

View 2 Replies View Related

SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option

Feb 10, 2015

So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.

From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.

View 3 Replies View Related

Credential Does Not Take Effect

Mar 13, 2008

I have a sqlserve, which service account is 'local system', running in machine A.

A credential ,which associated a windows user U1 in machine A, mapped to a sqlserver login Login1.

A file named 1.txt that only can be accessed by U1 in machine A.

A CLR procedure P1 that would read the 1.txt file.



I encounter a error when i access the 1.txt file through P1 as Login1:

A .NET Framework error occurred during execution of user-defined routine or aggregate "HelloWorld":

System.UnauthorizedAccessException: Access to the path 'E:1.txt' is denied.

System.UnauthorizedAccessException:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)

at System.IO.StreamReader..ctor(String path)

at StoredProcedures.StoredProcedure1(SqlString fileName)

.



I think it need a credential when a sqlserver login access some resource outside the sqlserver, so i add a credential and mapped to the login

any suggestions would be appreciated.
And please correct me if i have any inaccurate concept.


View 9 Replies View Related

HAVING Clause Has No Effect

Nov 12, 2006

I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.

It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF

GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))

SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END

Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?

Thanks.

View 3 Replies View Related

Trace Effect On Performance

May 23, 2001

When running a trace on a database, how does if affect that databases performance? Does it slow it down at all?

Thanks In Advance,

Chris

View 1 Replies View Related

Any Effect Of SQL Agent On REPLication?

Feb 4, 2004

Hello,

What happens to these REPLication agents if SQL Agent is stopped and started:
Snapshot Agents
Merge Agents
Misc Maintenance Agents

Can the agent be stopped and started with no thought to the status of replication, or should the replication state be modified in some way before any change to the status of the Agent?

(I know REPL depends on agent, so wuestion is, can REPL simply resume or not when agent is re-started.)

Thanks.
MichaelGG

View 2 Replies View Related

Upgrade Windows NT To 200, Effect The SQL 6.5?

Jan 31, 2004

If the server is upgraded to Windows 2000, will SQL 6.5 require to be upgraded to SQL 7 or 2000?

View 1 Replies View Related

Effect On Snapshots While Reindexing

Apr 30, 2007

Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.

View 1 Replies View Related

Same Effect As Cross Join?

Jul 7, 2006

How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?

Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.

I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...

I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.

Thanks in advance.

View 13 Replies View Related

Effect Of Joins On The Speed

Aug 6, 2007

Hi..

I want to know that when I do something like..

Select Query
Left Join
Select Query 2
Left Join
Select Query 3

How does it work actually?
As in, whether Query 2 & Query 3 will work only on the records retrieved by Query 1 only.
Or, all the select statements retrieves all the records and then the condition is applied to filter out the results.

Also, does the order of the Select statements make any difference on the speed?

Thanks




View 4 Replies View Related

What Kind Of Performance Effect Do Constriants Have.

Dec 7, 2004

We have a database that we have designed so all the data dependances are managed by the front end code. However the company we are writing it for has asked us to add around 50 constraints. I was just a little worried what kind of effect on performance this would have. Cheers Ed

View 1 Replies View Related

OSQL DELETE Using LIKE, With A Cascading Effect

Nov 16, 2006

Edit: Sorry This is OSQL.What I use as my query is:"DELETE FROM timerecord WHERE Actual_Time_In LIKE '11.12.2006%'"The row of Actual_Time_In is formatted with Date and time (MM.DD.YYYY HH:MM:SS) sometimes there are ten records and I'd rather not have to remove them from the table one at a time. However, even though I have a record that is '11.12.2006 22:43:00' my delete doesn't work osql states I have 0 rows affected.This is only MSDE so I don't have anyother way to open the table.Sometimes these records have other records that reference them. Is there anyway to do a cascading delete without it getting to complex?Thanks of all your help, I am just a tech support guy beating his head against a wall..

View 2 Replies View Related

Does JOIN Order Effect Efficiency?

Feb 5, 2007

Hi all,

Does JOIN order effect efficiency?

If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).

Thanks in advance,

Chiz.

View 1 Replies View Related

Effect Of Shrink Database Question

May 1, 2008

Hello all, thanks in advance for any advice here.
My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's?
Thanks for any guidence on the usage of this.

Bob
Not a downstroke, fistpicker

View 6 Replies View Related

The Collapse/toggling Effect In SSRS

May 27, 2008

Hi Guys,

I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?

View 1 Replies View Related

PRINT Only Takes Effect After T-SQL Completion ?

Jul 20, 2005

Hello All,I have a stored procedure which will act like a main/controller scriptwhich will then invoke more stored procedures (~20). Basically, itlooks something like below:-- start scriptcreate procedure ...print 'process started'exec sp_1exec sp_2exec sp_3....print 'process ended'-- end scriptLooking at it, after running that procedure, immediately I would expectthe first PRINT statement to be printed but it won't. It seemedthat the print statements would only display the messages afterthe whole processing completed.What's the reason for this sort of behaviour. If so, then we wouldnot be able to print any progress reporting in our scripts.Please comment.Thanks in advance.

View 4 Replies View Related

SQL Server Setting Has Any Effect On Queries?

Jul 20, 2005

Hello,When I try to run the following query on two different SQL Servers,I get error on one of the server (both has same set of data). I wastrying to get rows for ProductCode='XYZ_Q1'.SELECT ProductName, ProductType, ProductDesc FROM Product WHEREDepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,CHARINDEX('_', ProductCode)+2, 1) AS int)=1Example dataProductCode|ProductName|ProductType|DepartmentID|P roductDescXYZ_T_1|Test1|1000|12|TestXYZ_T_2|Test2|1000|12|TestABC_T_1|Test3|1000|11|TestABC_T_2|Test4|1000||11|TestXYZ_Q1|Test5|1000|12|TestABC_Q1|Test6|1000|11|TestIt's trying to cast all values under 'ProductCode' column instead ofapplying to subset with condition 'DeparmentID=12 AND ProductType >2000 'I solved the problem by equating it to whole string rather thantrying to extract the integer part of it. But I wanted find-out reasonas to why this is happenning.Is there any SQL Server setting that's causing this?Thank you very muchManchaiah

View 1 Replies View Related

Permissions Grants, Denies Don't Take Effect

Sep 11, 2006

Hi,



I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for €˜public€™ role and individual sql logins). I€™m not getting any error messages. Permission changes just don€™t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.

How can I change the permissions?

View 4 Replies View Related

Why The Report Parameter Dose Not Take Effect?

Nov 6, 2006

Hi, all experts here,

Thank you very much for your kind attention.

I am encountering a problem with reports built on my SQL Server 2005 Reporting Services Server. The reports parameters do not take effect at all? I chose the value from the report parameter list, but the result returned the whole report data. Why is that? And how can I solve this problem? Really need help.

Thanks a lot in advance for any guidance and help.

With best regards,

Yours sincerely,

View 11 Replies View Related

Change SA Password. Any Effect To Replication?

Sep 11, 2006

Dear all,

I have some questions regard subject above. I want to know the impact of replication process after SA pasword been changed.

Example: server1 (hq), server2 (district) & server3 (district).

If server1, SA password been changed, is it server2 & server3 need to change too?

may i know where is it the replication SA password stored? is it replication script refer ID (SA) from Security -> Logins?

I hope someone can help me.. thanks.

View 1 Replies View Related

Does ALTER COLUMN Effect The Data?

Aug 10, 2007

I just want to change the length of 2 fields. They're CHAR and NVARCHAR type. I want to change the length from 50 to 75.

Will doing this effect the data? Will it delete the data in those columns? I just want to make sure before I do anything. The table contains about 2.5 million rows, so I don't want to mess anything up. I'm aware that the script may take a while to run and finish, but I'm only concerned about data integrity.

Here is my example script syntax to show what I'm planning to do:




Code Snippet

alter table credit alter column writeoffreason char(75) not null;




Does it matter what type of data is in the columns? For now, it's only CHAR and NVARCHAR.

I also read somewhere that you can increase length but cannot decrease without repercussions?

Thanks!

View 3 Replies View Related

Allow Blank Value Does Not Effect When Parameter Is Multi Value = Bug!

Jan 3, 2008

I build reports using reporting services 2005 sp2.
I have 3 parameters---> 3 multi value combo box .
However when I tick "allow blank value" within the report parameter
properties for this drop menu, report WILL NOT run without making
selection.(The xml is ok allow blank value set to true)
the reports still require you to enter a value for all parameters in the report when the report can and should be able to be run with no parameter defined.
the trick "default values for all of the params to "=String.Empty"" is not good .


I found this article but i can't find microsoft hotfix for it .

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125877


"Thank you for filing the bug. This issue has been fixed for the next CTP.
Posted by Microsoft on 8/11/2005 at 7:00 PM"

Am I missing something obvious here?

View 1 Replies View Related

Need To Know Why Two Calculation Methods Result In Different Rounding Effect

Feb 23, 2004

Hi All,

I have a view that does some calculations on data in the database. Note in the two code snippets, that the columns being used in the calculations are all type FLOAT(53).

My confusion stems from the fact that both code snippets should be functionally the same in my "view" (*snicker* I kill me...), but they return different results which I think are related to rounding issues.

The first snippet:

CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2) * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate
GO

and it's result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004781.11774.17769.53527896
112/20/2004757.97750.36742.93605740
122/20/2004509.92501.72494.854180516
132/20/2004988.23980.65973.58632337
142/20/20041283.261269.571259.37416145


And the second snippet:

CREATE VIEW dbo.VIEW_Calculate_PortfolioIndex
AS
SELECT PP.PortfolioID AS PortfolioID,
PP.CreateDate AS CreateDate,
(CloseIndex * PP.AvgHighPriceRatio) AS HighIndex,
CASE WHEN PPI.CloseIndex IS NULL
THEN 100.00
ELSE ROUND((PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100.00)), 2)
END AS CloseIndex,
(CloseIndex * PP.AvgLowPriceRatio) AS LowIndex,
PP.Volume as Volume
FROM dbo.PortfolioIndex PPI INNER JOIN
dbo.PortfolioPerformance PP ON
PPI.PortfolioID = PP.PortfolioID AND
PPI.CreateDate = PP.PrevDate

which returns a different result set:
PortfolioIDCreateDateHighIndexCloseIndexLowIndexVolume
102/20/2004784.52774.17772.89527896
112/20/2004755.64750.36740.64605740
122/20/2004512.43501.72497.294180516
132/20/2004989.77980.65975.1632337
142/20/20041285.991269.571262.05416145


Specifically, I am concerned with the HighIndex and LowIndex values...since the only modification between the two code snippets is that in the second one, the HighIndex and LowIndex calculations use the column name of CloseIndex (as calculated in the select) in the calcs for those two columns, rather than repeating the code used to calculate the CloseIndex column's value.

I am confused as to why the results of the HighIndex and LowIndex caculations are different in the two selects, when the only change (in my view/expectations) is that one references the CloseIndex column, and the other one just reproduces the calculation itself

*scratching head*

any thoughts? Thanks,
Paul

View 14 Replies View Related

Report Model Item Security Has No Effect

Apr 25, 2007

Hi,



I am trying to use a very easy and simple feature of a reportmodel, model item security.

In my example i have two users; HGHJohn and HGHJKooi



I want to test if I am able to restrict access in the model to a whole entity. HGHJKooi shouldn't be able to see the entity 'Customers'.



These are the steps I executed:

1. In Sqlserver management studio I opened the properties of my model and navigated to the tabpage 'model item security'.

2. I activated the option 'secure individual model items...'

3. In the root of the model I declared two users(groups) as specified above

4. Automatically all nodes inherit these settings from the root.

5. For the entity 'Relations' I change the default, by selecting 'use these roles for each group or user account'

6. I removed HGHJKooi from this list, leaving only 'HGHJohn as model item browser



What I expected at this moment is that when I login the system as HGHJKooi, then I won't see this entity, but I still can! Does anybody know a solution to this problem?



Julian Kooiker

View 1 Replies View Related

DPI Setting When Rendering To PDF Has No Effect On Embedded Images

Apr 7, 2008

A report contains images rendered at 300 DPI. This DPI is fixed, in order to achieve the required quality of the image when printed.

Rendering the report (locally, using LocalReport.Render) as a PDF, with the DPI set to 300 in the DeviceInfo string does not appear to have any effect as the images in the resulting PDF are rendered at 96 DPI.

One solution is to render the report as a TIFF, which does have the desired effect. However, it is a PDF that is required, and converting the TIFF to PDF is problematic in itself. In addition the conversion of the TIFF to a PDF (using PDFCreator) results in a black that is now a blue-black instead of true black.

Any suggestions?

View 4 Replies View Related

Effect Of A SELECT Stored Procedure On Tables Or Database

Jul 25, 2007

Hi all,
I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.
For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed? 

View 4 Replies View Related







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