I have a server which is not running optimally and I checked the default trace. I have around 600 entries in the default trace which are all Missing Column Statistics and the database is tempdb.is_auto_create_stats_on and is_auto_update_stats_on are both 1 for tempdb.
View 2 RepliesGetting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
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 RelatedMy question: Is it okay to drop all the auto generated column statistics? (for the following scenario)
- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A).
- Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records.
- We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
I was in the process of migrating a server from one physical box to another. They are identical drive setups, same OS (2003), same SQL install (2005). Our server team did a 'PlateSpin', which copies the drives from one server to another, as long as the files are not in use. I did not reinstall SQL on the new box, i let the 'PlateSpin' tool copy everything over for me. I then stopped the SQL services on the old server and new server and copied over all of the system database (.mdf & .ldf) files. As soon as i started up the services on the new server, it looked great with one exception. The TempDB was only showing one datafile. When i queried sys.master_files, it was showing me 8 TempDB files. I tried restarting the services, but i still saw the same, only 1 file. I then tried to re-add TempDB files with the same name, but it would error saying they already existed. In turn, i could add new files with different names and they showed up fine. However, on a restart, they would not show up in the properties of the TempDB.
When i queried, sys.master_files again, i now had 16 Temp db files listed in the results. I deleted all but the original single file that was recognized out of the sys.master_files table and re-added the additional 7 files with he original names, restarted the service and then they all appeared.
statistics in sql server. how to create it and update it on tables.?
View 9 Replies View RelatedI'm designing a new database which will be the back-end to a heavily-used web-based application (all these terms are relative - I guess the use won't be that heavy in the grand scheme of things, I'm only talking 100 users or so at the very most). Data from the old application database will be migrated to this one, and the old database is around 7GB in size after 5 years of use.
I have two different ways of linking some tables in mind, one which is slightly more complex than the other but which potentially has benefits over the simpler method. However, I'm concerned that I might be 'over-cooking' the design, and that performance would suffer as a result, so I've tried creating the two different versions of the database (the part of it I'm concerned with, anyway), one for each of the solutions I've got in mind, migrated the data into the relevant tables and carried out some queries on the data to collect some statistics.
The problem is that, whilst I can see that the more complex method is more expensive, as expected, I don't really understand if the difference is significant. Since I don't know what the numbers in the Client Statistics window actually mean (there are no units! I'm guessing times are in milliseconds?), or how much of real-world impact the difference will have, I'm finding it hard to interpret my statistics and come to a decision.
Querying the entirety of my tables to return ~20,000 records listing one column from each of the main tables I'm playing with, the simpler method had a Total Execution Time of 199, and the more complex a Total Execution Time of 272. Is that the statistic I should be most concerned with? Is that a difference I should be concerned about? Is the difference likely to be magnified when the database is much larger and in use, such that a difference of 73 milliseconds in this test scenario could end up being as much as a whole second in production, for example?
I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this
IF ( update_statistics going on)
dont do anything
else
run update statistics
This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.
Seen activity like this? If so, where does it come from?
dd hh:mm:ss.mss:00 00:18:32.210
session_id:79
login_name:meme
wait_info:(6ms)IO_COMPLETION
CPU:646,180
tempdb_allocations:2,088
tempdb_current:0
blocking_session_id:NULL
reads:171,237,095
writes1,439,934
physical_reads:637,080
used_memory:2
status:rollback
I have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.The report is at a table and field grain to include values for:
* Min column value
* Max column value
* Number of discrete values
* Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.There must be a better way of doing this and I don't have access to any DQS services.
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
Hello,A script we run against the database as part of the upgrade of our productis failing with the following message:ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3accesses this columnThe line that fails is:alter table Badge alter column EncodedID nvarchar(16)It's clear that there's some kind of automatically generated statisticsobject referencing the column that prevents us from changing it from anint to an nvarchar. However, I have no idea how that got there - my bestguess would be that it has something to do with the auto generatestatistics option being set on the database. However that seems odd to mebecause we've done lots and lots of work like this and not encountered theproblem. It also seems like a quick fix would be to perform:Drop Statistics Badge.encodedidHowever I am afraid subsequent statements might fail on this db since Iwasn't really expecting this in the first place. Does anyone have anyinsight?Dave
View 3 Replies View Related
I need to know definitively when I need to drop and recreate column statistics (histogram) when altering a column. Empirically, it seems I can lengthen a varchar or change a not null column to a nullable one, and then existing statistics dont' cause
Msg 5074, Level 16, State 1, Line 1
The statistics 'c1' is dependent on column 'c1'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c1 failed because one or more objects access this column.
But, if I change a column to not nullable or shorten a column, I get the error.
Is this the complete description of when I need to drop and recreate column statistics and when I don't?
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
View 2 Replies View RelatedAt one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
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
It is possible that Data Collection can cause massive increasing MB/sec to tempdb ? I cannot find connection with tempdb and I set cash file, but on same disk.
Or it can be something different? Last two weeks what I checked was Read/Write MB/s to tempdb increasing progressively.
One time it was about 20MB/sec
After it was reseting and again 1MB/sec..
What I checked , External company which install SQL Server made one file for tempdb, next week or during breaktime(it will be possible), I would like make 8files next weekend work.
Now I saw that TempDB mdf was still increased, but using was just 8-10%
I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...
USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1
Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232
Error:-
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note:
=>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...
=> I executed above commands and restarted services...no change...
How to remove / drop ndf file...
I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...
I can get a snapshot of tables in tempDB, but I would like to track which procs are causing the load in the tempDB.
I think I can sample and record objects in the tempdb, but I would like to record the proc creating the most tempDB usage, and disk read/writes associated with those procs.
The DMV's give usage in the individual DB's, but what's a good way to correlate procs in the DB's to tempdb usage?
Is it possible to encrypt Master database and tempdb? On executing below query result is showing temdb is encrypted.
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.
I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.
For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).
Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.
But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.
While trying to move the tempdb log file to a different disk, I mistakenly executed the following and then stopped and restarted SQL Server.
USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'L:MSSQL11.MSSQLSERVERMSSQLData emplog.ldf')
GO
Now I can't start SQL Server and see this error in the log files:
[code]
2015-08-10 15:28:42.55 spid7s Error: 5171, Severity: 16, State: 1.
2015-08-10 15:28:42.55 spid7s L:MSSQL11.MSSQLSERVERMSSQLData emplog.ldf is not a primary database file.
2015-08-10 15:28:42.55 spid7s Error: 1802, Severity: 16, State: 4.
2015-08-10 15:28:42.55 spid7s CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.
[code]
I did not have remote connections enabled yet, so the resolutions I have found that include sqlcmd or starting in single user configuration are not working. Any way that might allow me to restore the usual tempdb settings, which I think would allow SQL to start again?
I am seeing select * from sys.sysprocess where blocked<>0 is always the report server
ReportServer.dbo.GetSessionData;1 is blocked by ReportServer.dbo.WriteLockSession;1
We have different reporting servers using same database.
Is it require to rebuild indexes on report server and report server temp db on daily basis?
I need a script to Backup & Shrink tempdb.
namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
I have a tempdb split into 4 files (5 if you include the log).
Autogrowth is disabled on the mdf/ndf files so that they can be used round robin (1 file per logical CPU).
Is there a way to be alerted when there is x% of free space left?
I know hwo to check the free space via t-sql but want to be able to be alerted. I could run a sql job that reports the free space and send a database mail message if under x% but wondered if there was a built in (or better) method?
I also have SQL Sentry.
SQL 2012 Standard
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
I was in the process of creating additional TempDB.ndf files, and received an error saying they already exist. I checked the location and it was empty, nothing to see here. So I looked in sys.master_files and there are several tempdb files listed in various locations, all of which come up empty.
So the files are listed as online in sys.master_files, but they do not exist on the server. I restarted SQL services but it did not change anything.
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
How to solve the below blocking issue
One of our production server is configured with Always on with sql cluster.
We are getting frequently blocking
Waiting Resource Key Type: Key
WaitType: LCK_M_S
WaitResourceDatabaseName : ReportServerTempDB
WaitingSessionProgramName: Report Server
BlockingSessionProgramName: Report Server
WaitCommandType: Select
WaitingCommandText: Create Procedure dbo.checksessionLoak @sessionID...
how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?
View 2 Replies View Related