hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.
however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:
this is a query for one of the statistics i am currently using:
SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID
FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID
WHERE UserDescription.Status=1 AND UserDescription.UserType=1
GROUP BY DefendantCase.UserID
if i run this query, this is what i get:
UserID CountOfUserID
54 21
60 10
52 29
4 4
27 22
how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!
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
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.
Can I get statistics on which type of DML statements (e.g. insert, delete, update) that are executed by users on a table without creating triggers? I want to be able to show the number of executed statements per statement type. I have tried the 2005 Profiler but it outputs the entire batch statement which makes it a bit more difficult to create statistics.
I need to make a backup of a table so that if I mess it up I can put the data back to where I started...
I am going to insert some new rows and update values in some existing rows. So before I do this and find out I messed it up...how do I make a copy of Table A as Table B?
I would actually like to make a copy to table B, then run my changes into B, make sure all is correct, and then transfer the revised data from B over to table A replacing its data. What approach would be best here to accomplish this?
We recently upgraded one of our servers to SQL 2005 - SP2. This server is a subscriber. Both the publisher and the distributor are SQL 2000. We are now having issues with some of our Snapshot publications. One of the tables in the publication has statistics created on it - not sure how they were created. This table is small - has a PK on it but no other indexes - this is similar to other tables in the same publication that aren't having issues. When the snapshot runs each night it tries to recreate these stats on the tables. Since they already exist we get and error saying There are already statistics on table XXX named ZZZ. We can go in and delete the stats on the table and then replication will go through and recreate them but obviously this isn't something we want to do everyday.
Requirement: I wanted to create another table based on the column values. For eg: I have to take the employee id and check for what value he has under owns column in the table. I take only 3 values and then these values should go to the newly created columns (owns1, owns2,owns3). if there is no value for any of these columns it should have null values loaded in them. The result of the modification should look like this:->
Note: eventhough employeeid 3 owns more than 3 things we only take 3 of what he owns and populate to above coloumns. In addition to it, the column OWNS will have more than 500 different values in them.
Its kind of urgent and if anyone knows how to , Can you please help me on this. Thanks a lot. -- Ragulan ;)
I have a huge question, it's for my job im doing now. I have a table with the name TWO.dbo.SVC06105. I want to be able to take the word "TWO" and put that into a variable. I know this doesn't look correct, but I want to be able to do something like this:
DECLARE @GPNAME CHAR(100) SET @GPNAME = 'TWO'
SELECT * FROM @GPNAME.dbo.SVC06105
I know the SELECT statement isn't correct, but I hope that you guys understand what i'm trying to do through this example. BTW, I am new to this team so nice to meet you all. L8er
Hello, Im a very new to SQL server etc, so please bear with me.
I am currently trying to find a quick way of making a large number of database entries within a particular table all have the same value in one particular column.
I have created a query in MS SQL Server Management Studio, which outputs all the entries in a particular table that I want to change. Currently they all have different values in a certain column in this table, and I want them all to have the same value in that column.
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID Name Location Start End
1 Joe NY 2000 2001
1 Joe CA 2002 2004
1 Joe MA 2005 2008
2 Sue NJ 2003 2004
2 Sue FL 2004 2008
3 Bob CA 1999 2000
3 Bob WA 2001 2004
3 Bob OR 2005 2006
3 Bob MI 2007 2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID Name Location1 Start1 End1 Location2 Start2 End2 Location3 Start3 End3 Location4 Start4 End4
1 Joe NY 2000 2001 CA 2002 2004 MA 2005 2008
2 Sue NJ 2003 2004 FL 2004 2008
3 Bob CA 1999 2000 WA 2001 2004 OR 2005 2006 MI 2007 2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.
'eligibility (dbo)' table - Unable to create index 'PK_eligibility'. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Any ideas on how to make the table change more efficient or change the timeout period. I need to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4, [8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8, [12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12, [4/1/2008 12:00:00 AM] AS Month13, Engineer FROM (SELECT Client, DollarsBilled, SlipDates, Engineer FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM], [6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM], [12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
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.
As part of my automagical nightly index maintenance application, I am seeing a fairly regular (3-4 failures out of 5 attempts per week) failure on one particular table in my database. The particular line which seems to be failing is this one:
DBCC SHOWCONTIG (WON_Staging_EPSEst) WITH FAST, TABLERESULTS, ALL_INDEXES
The log reports the following transgression(s):Msg 2767, Sev 16: Could not locate statistics 'WON_Staging_EpsEst' in the system catalogs. [SQLSTATE 42000] Msg 0, Sev 16: [SQLSTATE 01000] Msg 0, Sev 16: -------------------- Simple ReIndex for [WON_Staging_EpsEst].[IX_WON_Staging_EpsEst] [SQLSTATE 01000] Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] Msg 0, Sev 16: [SQLSTATE 01000] Msg 0, Sev 16: -------------------- Post-Maintenance Statistics Report for WON_Staging_EpsEst [SQLSTATE 01000] Msg 0, Sev 16: Statistics for WON_Staging_EpsEst, WON_Staging_EpsEst [SQLSTATE 01000] Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] Msg 0, Sev 16: Statistics for WON_Staging_EpsEst, IX_WON_Staging_EpsEst [SQLSTATE 01000] Msg 2768, Sev 16: Statistics for INDEX 'IX_WON_Staging_EpsEst'. [SQLSTATE 01000] Updated Rows Rows Sampled Steps Density Average key length -------------------- -------------------- -------------------- ------ ------------------------ ------------------------ Aug 3 2007 3:22AM 674609 674609 196 2.0958368E-4 8.0
(1 rows(s) affected)
This table is dropped and recreated each day during a data import job. After the table is recreated and repopulated with data (using a bulk import from a flat file), the index is also recreated using the following code:CREATE INDEX [IX_WON_Staging_EpsEst] ON [dbo].[WON_Staging_EpsEst](OSID, [Year], Period) ON [PRIMARY]Yet more often than not, that evening, when the index maintenance job runs, it fails with the aforepasted messages complaining of being unable to find table/index statistics.
Worth noting, perhaps, is that this same process is used on roughly 10 data staging tables in this database each day, and none of the other tables fail during the index maintenance job.
Also worth noting, perhaps, is that this IDENTICAL table/code is processed in exactly the same way on TWO other servers, and the failure has not occured in any of the jobs on those other two servers (these other two servers are identical mirrors of the one failing, and contain all the same data, indicies, and everything else.
Any thoughts, suggestions for where to look, or unrestrained abusive comments regarding my ancestry?
I have a small doubt. If we apply a statistics command on a particular table what will it update. Normally statistics are created automatically by the server or we have to create it.
Anybody know how many companies worldwide use SQL server and how manyindividual servers this amounts to? Also, at what rate is SQL usegrowing? Can someone at least point me to a source where I could findclose to exact numbers?
Here my data sample on which I need to perform some stats Time(Sec) Result 1 2 2 8 3 6 4 2 5 2 6 4 7 2 8 7 9 8
What I need from this is a result set that looks as follows GroupNo Value 1 5.33 2 2.67 3 5.67
This is a grouping of the result data in 3's by time. Value is the average of the Group. In need to write a select statement to do this. Note the Group could be done from 1 to 10
The end result of this is to display a Range Chart which shows Results grouped according to requirements. Any Help would nice. Pargat
I would like to know, How can I drop Statistics from tables. My user tables has two indexes and and some statistics created onto them. I would like to drop the statistics indexes and apprecaite, If someone please advice.
What are some ways to analyze index coverage and usage? I have a 18 GB database, half is data, other half is indexes and I want to cut down that number as much as I can without affecting performance. Thanks
Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....' indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!
I have been monitoring some indexes on a table with a lot of inserts, no updates and no deletes. I was wanting to determine when to update the statistics on the index. Does anyone know what would be a good target range for the density when you run the dbcc show_statistics?
When the "create statistics" command is run, what table entries are made into system tables?
I want to check for the existence of statistics on certain columns and if they are not there, create them. What is a good way to see if they are already created?
I was wondering how often you should reindex. By looking at dbcc showcontig and statistics I see that I am heavily fragmented and scan density is between 10-30% on my important indexes. I'm thinking of scheduling this to be done nightly. nay help is much appreciated.
I am contemplating creating a job to execute every 5 mins which will update index statisics if they are more than say 8% out. I would like to know what thoughts people have on this? i.e. pros and cons.
I like forward to what you have to say.
I have auto stats on. Our stats are often more than 10% out. At what level do you reckon the query plan might be effected by out of data stats?
