I'm fairly new to SQL Server and I'm just wondering if it's possible to Update Statistice for all indexes somehow? I'm looking at the Update Statistics command and it doesn't seem to be possible.
The situation we have is a reporting DB that basically has all it's tables truncated and remade every night by some DTS jobs that import from another datasource and change the data and build some denormalzed tables etc.
Some of the large Insert operations go from taking 8 mins to taking several hours sometimes and updating the stats seems to fix the problem for a while. So I'd like to make sure the optimizer has all the latest stats for all tables.
I have a Hits table that tracks the hits on the id of a Link table: Hits: int linkId (foreign Key to Link) datetime dateCreated varchar(50) ip We recently had to merge Links from different systems that are implemented similarly. As a result, all the linkIds are now wrong in the Hits table because the ids all changed. I managed to track down all the old ids and their associated new ids and have it in a table that I call joined_links joined_linksint oldId int newId
So, how do I do a mass update of these linkIds in the Hits table in SQL? I know I could do it in .NET, but I'd rather not write an app to do that runs thousands of update statements. There's gotta be a way to do it something like this:
UPDATE Hits h SET h.linkId = (SELECT newId FROM joined_links WHERE oldId=h.linkId) but obviously I don't have visibility of that linkId in the subselect... A Loop maybe?
This is a run of the mill application that moves orders from one table to another. There are 2 tables, Ordersummary & HstOrders. Ordersummary has the following columns... Identifier FollowupId OrderNumber OrderReference OrderReferenceOrigin ...... ...... HstOrders has the following columns... Identifier OrderNumber OrderReference OrderType ...... ...... The above two tables are bound by Identifier. After each month end, Orders are moved from Ordersummary to HstOrders.
Now my task is to update all rows in OrderSummary with the order details as seen in HSTOrders for ordertype = 'CREDIT'. OrderReferenceOrigin(in Ordersummary) should be updated with the value of Orderreference(from hstorders).
I have to update each row at a time & I need to write a cursor for mass updates where ordersummary.identifier = hstorders.identifier.
Can someone please help with in writing this update statement as I never wrote a cursor.
If I have a trigger on a field in a table, and I update one record trigger fire properly. If I do a update to that same field on all records in the table the trigger does not fire. I the error in the trigger, or do I need to change my update statement?
Hi,I need to update a field in about 20 records on a table. The table hasan update trigger (which updates the [lastedited] field whenever arecord is updated). As a result I'm getting an error: "Subqueryreturned more than 1 value.", and the update fails.Is there a way in the stored procedure to handle this issue?thanks for your help.Paul
Hola!I'm currently building a site that uses an external database to store all the product details, and an internal database that will act as a cache so that we don't have to keep hitting the external database to retrieve the products every time a customer requests a list.What I need to do is retrieve all these products from External and insert them into Internal if they don't exist - if they do already exist then I have to update Internal with new prices, number in stock etc.I was wondering if there was a way to insert / update these products en-mass without looping through and building a new insert / update query for every product - there could be thousands at a time!Does anyone have any ideas or could you point me in the right direction?I'm thinking that because I need to check if the products exist in a different data store than the original source, I don't have a choice but to loop through them all.Cheers,G.
Has anyone noticed a performance improvement during trading hours when they replaced sp_updatestats with UPDATE STATISTICS FULLSCAN in their nightly maintenance? Or is it negligible?
Does anyone know how to tell how long it took for an auto update statistics to run? I looked under DBCC Show_Statistics and it shows the time the stats were last updated, but not how long it took to update them. Thanks.
I am having the following errors with the script below
Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '?'. Msg 319, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. it also does not seem to loop around each db
Recently we moved few of our databases from SQL 2000 to SQL 2005 (SP 2) using backup and restore. After the restore I did Reindex and update stats on the databases. Since then we have observed performance issues on SQL 2005 databases but this performance problem vanishes the moment we run (sp_updatestats). Is this a problem with SQL 2005 that we have to run sp_updatestats 2 times a days or 3 times a day. In SQL 2000 we ran it only Once a week and still we never had any performance issues. Is there any config change we need to do to fix this problem in SQL 2005?
(Assuming SQL Server 2000, auto create statistics on, auto updatestatistics on.)DoesDBCC DBREINDEX(<tablename>)update statistics? If yes, are the statistics equivalent to thosethat would be produced by:UPDATE STATISTICS <tablename> WITH FULLSCAN
Recently a production server suffered a critical blocking period and I wanted to know if I could solicit some input. It seems that a stored procedure was in the middle of recompiling while and auto update statistics started. This caused blocking for like an hour on the single object (stored procedure) that was originally called. The table that the update occurred on and that the stored procedure is reading form is quite large. It is 2 mil rows and about 140 columns wide. Some info from sysprocesses is below. The table alone takes up almost 4GB of space, when looking at sp_spaceused. I have some questions. 1. Can the update statistics for a '_WA%' stats cause blocking on a table? 2. Does an update stats on an index survive a restart of SQL server? We tried restarting, but the blocking did not end. 3. If the stored procedure is running under a compile, can the server automatically start an update stats and cause the stored procedure to wait? 4. Can the server automatically start an update stats on more than one column stats at a time, causing one to be blocked by the other? 5. We had never seen this issue before going to SQL2K clustering. Is this something specific to SQL2K and not SQL7 ?
Thanks for your input. John Lee
This is the lock info for the blocking processes.
spid dbid ObjId IndId Type Resource Mode Status name ------ ------ ----------- ------ ---- ---------------- -------- ------ ------------------------- 142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes 142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes 142 7 421576540 0 TAB Sch-S GRANT tJob 142 7 1141579105 0 TAB Sch-S GRANT tPatient_info 142 7 1141579105 0 TAB [UPD-STATS] Sch-M GRANT tPatient_info 142 7 1659921035 0 TAB [COMPILE] X GRANT iDBGetPatInfoRecord 142 7 1659921035 0 TAB Sch-S GRANT iDBGetPatInfoRecord
These are the processes that are being blocked:
spid ------ 137 140
Below this is a snapshot of all the SQL processes on the server being blocked. Save the report and send to the whole database group.
Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?
How do i update the stats of tables when we insert data into it. I believe Auto stats update happens only when 500+ 20% of the rows are changed for a table. Once we insert say some 1000 records in to a particular table the query time takes too long (more than 1 min). The same query executes faster once i manually update the stats.
I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
We have a file import job. This job typically imports millions of records into a SQL2008 DB. After the load the DB performance goes down the drain. Thus far, their solution has been to rebuild indexes on effected tables. I'd like to come up with a better solution. My guess is that after the load, the statistics are shot until the next stats update.
What is the best way to handle this scenario? There must be some way to keep the stats current during a big data load.
We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.
Apart from above, any other things to be in place for better maintenance of the sql server.
Also, how to Index Rebuild activity for clustered indexes requires any downtime.
When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)
The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB
So my question is this...
For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.
Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index. 2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
We have recently migrated quite a databases around 20 from SQL 2000 and 2005 to SQL server 2008R2.
I am using Ola's script for index maintenance for those with compatibility level above 80 as i heard it supports that way.
Hence separated in 2 way job where for those with compatibility level 80, we are running job with below query on each database with 80 as compared
USE ABC GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO
I am not sure if this is the only way in for those databases, because we are seeing the database getting because of that somewhere using above query.( seems log file filling very rapidly).
But above is not the case with those databases , with compatibility 90 and above.
I have a series of DTS packages. Each package has 20 queries. Each query has a server name. Is there a way to change the servername without editing each query in each DTS package. I'd like to copy the template DTS package, then perform the modification.
I have an user table with a single integer column. No indexes, no identities, nothing. I have to insert 600,000 rows via a client app. In tests, using BCP/Bulk Insert/DTS all runs OK (sub 3 seconds). However the app takes 5000 rows a second [considerably slower]. I can mimic this slow perfomance, within DTS, by removing the 'Fast Load' & 'Batch' options.
Question= why would the SQL insert run slower on one server and as fast as BCP/Bulk Insert/DTS on another? What can I check on the 'slow' running server? May there be a file version anomally ??
Does anyone know what the best way to do mass updates in SQL server is? I am currently using the methodology suggested in this article
http://www.tek-tips.com/faqs.cfm?fid=3141
But the article is assuming that once I update a field it is going to have a value that is NOT NULL. So I can loop through and update the rows that have a NOT NULL value. But my updated rows do contain NULL values, in this case what is the best way to go about this???
*************************************** Here is my code. I want to avoid using Upd_flag becos after the following code runs I need to reset that flag before I run my next query ***************************************
--Set rowcount to 50000 to limit number of inserts per batch Set rowcount 50000
--Declare variable for row count Declare @rc int Set @rc=50000
While @rc=50000 Begin
Begin Transaction
--Use tablockx and holdlock to obtain and hold --an immediate exclusive table lock. This usually --speeds the insert because only one lock is needed.
update t_PGBA_DTL With (tablockx, holdlock) SET t_PGBA_DTL.procedur = A.[Proc code], t_PGBA_DTL.Upd_flag = 1 FROM t_PGBA_DTL INNER JOIN CPT_HCPCS_I9_PROC_CODES A ON t_PGBA_DTL.PROC_CD = A.[Proc code] WHERE t_PGBA_DTL.Upd_flag = 0
--Get number of rows updated --Process will continue until less than 50000 Select @rc=@@rowcount
I have the following problem. I need to insert 100.000 records (50Kb each) in one operation from a VB program into a SQL Server 2005 database. All of these records will be ready for inserting at the same time. How to make this insert as one big transaction instead of 100.000 small ones?
The company i work for changed names and all email addresses within the company have changed. While it was OK for a while they are no longer going to be forwarding email to the old addresses to the new ones. There are Tons of subscriptions and tons of email addresses that need to be changed to the new names.
If i could find the table with the TO: part of the subscription held in it i could just run an update on that field and it would be solved...however, i cannot find that field...
So, Without going into every subscription in report manager, how can i change the email addresses? Any Suggestions?
Most of the time my solutions consist of 1 or 2 packages and config files work well.
Now i have a solution with about 50 packages i have to move to QA.
However a config file has the package ID, so even thoug they use the same data source connection. I would have to create and change 50 config files.
Data sources are kept in the package xml , so if i copy all the packages to QA , and then change the Global Data Source connection, i still have to open each package maually and save it again.
Surely there must be an easy way to move all 50 packages and have the connection now point to QA. But config files and global data sources dont do the trick, what am i missing here ?
I am making a prog that needs to import many records from a spreadsheet on a local computer through asp.net into sql server is there a simple command to do this or is there information on how to do this please give all the information that you can thank you
I want to backing up my hourly transaction log backups direct to a mass storage unit as opposed to the local server. However when trying to set this up it only gives me option of backing up to local drives, even though I have a drive mappping to the mass storage unit.. I'm there is a simple around this.Would appreciate any advice..many Thanks..
I was wondering if anyone knows of any way, including third party tools, to replicate a design change on a table across many different databases. I have written an ASP script that allows me to copy multiple tables to multiple databases in one go but I need something that will allow me to replicate the design of a table by comparing source and destination tables. So far I have scripted most of it but I have no idea on which system table to get the identity information and it seems there must be an easier way!
I have an existing SQL table that I want to import 300,000 rows into. I have copied the table headers into Excel and added all the rows but constantly getting multiple errors no matter what way I try to import it.
So far tried Import Method from SQL Management Studio, BULK INSERT and SQL Script. What is the easiest way?
If I try this
select * into Coupon FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:coupon.xls;HDR=YES', 'SELECT * FROM [coupon$]')
I get
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
