How To Select The Poor Indexes?
Jul 28, 2004hi,
I have a dba with more than 500 tables and I want to drop the indexes on the least selective columns. How do I know which are the poor indexes?
Please help.........thanks
ADVERTISEMENT
Select Indexes On Sql CE
Jul 21, 2006Hi,
I'm try to get the total number of the indexes in a Sql CE database table. Is there a way to 'select count(*)' the indexes?
Cheers,
Justin
SELECT INTO With Indexes?
Oct 27, 2007Is it possible to copy tables with SELECT INTO and let the created table have the same indexes as the original table copied from?
Thanks
Uwe
Using SELECT ... WHERE Column IN (..., ...) Use The Indexes !?!?
Jan 25, 2008Anyone can explain to me if the expression
SELECT ... WHERE Column IN (..., ...)
use the indexes or it's better to use the
SELECT ... WHERE Column = ... OR Column = ...
what is the best for the engine!?
Regards
Paulo Aboim Pinto
Odivelas - Portugal
Removal Of Selected Indexes / Script Index Create For List Of Indexes
Jul 1, 2014I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
SQL 7 Poor Performance
Apr 4, 2000Hi,
I have just upgrade my sql 6.5 to 7.0 version. This sql box (compaq proliant 5500) has 1 gig ram and 4 pentium pro processors and smart array controller. There are about 200 users hit on that server constantly. I configured this server according compaq white paper and Microsoft recommendation; however, I am still suffering huge performance hit. Then I setup performance monitor to see what is happening. What I see is all of 4 processors are at 70% processor time constantly. What I heard is sql 7.0 runs much better on pentium III processors. Is that true? Or you have any recommendations??
Help!!
Thanks!!
Jim Zhong
Poor Performance
Jul 23, 2005Hello Gurus,SQL Server 2000Windows 2003 Server, Standard Edition.Firstly I'm not a SQL server DBA but have a little experience withOracle 9i and Oracle Rdb.An application that I've inherited has started performing very veryslowly over the last few days, as far as I know there have been nomodifications or changes in the volume of data the db is holding. Thedatabase is in simple logging mode and it's updated twice per day fromand Oracle Rdb database.The SQL database is all in the primary filegroup and is sat on fourdisks (RAID mirrored) which form one logical disk of 128gb. Itconsists of approx 120 tables, each with a primary key and a number ofunique indicies per table.Apart from a twice a day update in the small hours the only access tothis database is read only. Users are connecting via the web andusing predefined Crystal reports to retrieve data.Questions then:Should this db have more than one filegroup and should I put theindicies in a different group? Is this relevant when the underlyingstorage is RAID?Should it be using mirrored RAID or should it be striped or should Isteer clear of RAID for a database?The indicies have not been rebuilt for some time (probably one year).Is this something I should be doing once per day/week as I do have thedowntime window?Should I be doing a database/table reorg at fairly regular intervals?I'm going to generate a workload file from SQL profiler and see what,in conjunction with the Index tuning wizard it suggests.Appreciate that poor performance & tuning is a huge subject and not anexact science but any tips or comments would be gratefully received.RegardsDave.
View 2 Replies View RelatedPoor Performance
Sep 24, 2007Hi
I have the following structure:
At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.
This is my problem:
The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.
I would like to know what problem can be cause this delay.
Thanks
Poor Performance
Feb 4, 2008Is there a way to improve this query?
The execution plan states that it's ,aking use of the Indexes as 'Clustered Index Seeks'
but the query takes 30 minutes to complete.
The Index statistics are also up to date!
If I use just an INNER JOIN the query completes in 2 seconds!
How can I make the LEFT OUTER JOIN more optimal?
SELECT A.MeterSerial AS 'PeaceMeter', B.MeterFrom, B.MeterTo,
CASE WHEN A.MeterSerial = B.MeterFrom
OR A.MeterSerial = B.MeterTo
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
THEN 'Y' ELSE 'N' END AS 'ComplexMeter', B.ComplexMeterType
FROM Peace.MeterData_DR1 A LEFT OUTER JOIN
(SELECT MeterFrom, MeterTo, ComplexMeterType
FROM Peace.ComplexMeters2) B ON A.MeterSerial = B.MeterFrom
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
WHERE A.MeterSerial != ''
Poor Performance From Web Server
Dec 18, 2007Hi,I am having a problem with one of my stored procedures in SQL Server 2005. Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET. I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it. More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig. I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.If anyone has got any ideas on this then please let me know as I am completely stuck. I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...Thanks in advance Tom
View 1 Replies View RelatedIs This A Poor Database Design?
Dec 29, 2004Setup
I am using a simple aspx page to calculate a person’s tax filing status and capturing statistical information. Most of the items are drop down boxes or radio buttons but a few are checkboxes.
Question
My plan is to give each option in the check boxes its own column in the database this will lead to a table that is 70 columns wide. Is that to many? Most of the info will be very small like “yes� or “no�. I will be using a stored procedure to insert the info. This will be coming down a VPN
Goal
I would like to give the admin staff here real time access to the data via link tables and such. I would like there to be only one table so it will be easy for them to pars the info quickly without needing to join tables or write unions. Ideally this would get me out of the business of creating one off reports.
SSAS Poor Performance
Jul 9, 2007Hi,
I created a cube in my development box, tested, performance is great, now if I try to deploy it into the production server from VS, the screen freezes without any error msg... after some time playing around with security I gave up and created a backup of the cube, and sent it over to the IT department( I have no direct control over the production server so they restored the cube ). Now with the cube in production, it was a matter of just processing it... well that didn't go well either... it would take forever from my development box to see results... then I tried to browse any dimension using the data already on the backup... a simple 4 values dimension would take 2-4 minutes to load on screen...
I can't understand why browsing the cube its so slow in the production server, IT admin even reported that when trying to browse locally it would be slow too... The server has 16gbs of RAM and its a dual processor, he didnt notice any lack of CPU or memory while browsing the cube...
Have you experienced this or can you help me troubleshoot whats going on?
Poor Man's Trace Replay?
Apr 23, 2008A while back I captured a large trace but only the SQL:BatchCompleted event and not the usual list of events so I'm not able to use the RML Utilities or Profiler to play it back. Really though all the queries are from the same login so there's no issue just playing them straight through I would think and using the stored duration to back calculate a start time and just play them all through. Are there any tools to do this or I am on my own having to whip something up?
View 6 Replies View RelatedPoor Query Optimizing
Nov 4, 2007Hi there,
I've got a SQL 2005 table(DataTable) with 5.5 million rows and a clustered index on an integer column. The column is a foreign key pointing to another table (LookupTable) that associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index.
I'm running equivalent select statements with slightly different where clauses, and am not seeing the performance similarities I'd expect.
select * from DataTable join LookupTable on DataTable.Integer = LookupTable.Integer
1. Setting the where clause to the nvarchar(50) value on the LookupTable takes 64 seconds.
2. Setting the where clause to Integer = (select integer from LookupTable where nvarchar(50) = 'mysearchstring') takes 61 seconds.
3. Setting the where clause to Integer = 526 takes 1 second.
Is this poor design in SQL, or is it a misconfiguration? I would have expected SQL to modify my query to essentially be #3 in all cases before touching the DataTable, which would cause all three versions to take 1 second.
Thanks,
Jason
Poor Performance Due To LCK_M_S?
Nov 13, 2007We just converted from SQL Server 2000 to SQL SERVER 2005 and it seems as though we are having trouble with our performance. Sets of queries that used to take about 15 seconds now take almost 2 minutes. We used a utility to find out what was taking so long and found that almost all of the wait time belonged to LCK_M_S. Does anyone have any suggestions?
Here is a snippet of code I used to test the speed against 2000:
declare @counter int
set @counter = 1
while @counter < 200
BEGIN
update UPR40500 set actindx = 96 where actindx = 96
set @counter = @counter+1
END
This takes 15 seconds when it used to take virtually no time at all.
Poor Index Performance
Mar 24, 2007Hey guys,
Having some trouble with indexes on sql server 2005. I'll explain it with a simplified example.
I have a customers table, and a sp to list customers :
create table Customers(
CusID int not null,
Name varchar(50) null,
Surname varchar(50) null,
CusNo int not null,
Deleted bit not null
)
create proc spCusLs (
@CusID int = null,
@Name varchar(50) = null,
@Surname varchar(50) = null,
@CusNo int = null
)
as
select
CusID,
Name,
Surname,
CusNo
from
Customers
where
Deleted = 0
and CusID <> 1000
and (@CusID is null or CusID = @CusID)
and (@CusNo is null or CusNo = @CusNo)
and (@Name is null or Name like @Name)
and (@Surname is null or Surname like @Surname)
order by
Name,
Surname
create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary
create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary
create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary
I've recently noticed that some tables, including 'Customers' don't have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like ('joh%' '%') but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using 'alter', works ok with CusNo, but not with name/surname. Recompile it, and it's reversed again. When run as a single query, the execution plan looks different.
What's happening? Perhaps something to do with statistics? This doesn't have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before...
Poor Performance On Oracle
Aug 3, 2006Attention: To all SSIS gods like Donald Farmer, Jamie Thompson, Michael Entin
I am so very disappointed with the performance of SSIS on Oracle. When I am doing a simple lookup, it is caching the entire lookup table that is killing my performance. What is worst? When I try to change from Full to Partial or None caching mode, the component throws an error (x) sign.
I am also so frustrated with the way I have warnings all over the place. The warnings are saying code page value for the column cannot be determined. But the code page value is defaulted to 1252. I looked around everywhere to find what the damn code page value of my Oracle database is without any luck.
The damn package that I created takes 10 minutes to process 10,000 records ONLY which is slower than a legacy Cognos Decision Stream!!! Unacceptable!!! My lookup tables on an average have 200,000 records and I DO NOT WANT TO CACHE THEM all for 10,000 records. Something seems messed up!!!
PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!
Linked Server = Poor Performance
Jan 10, 2006Using a query through a linked server is giving tremendously reduced performance.
Is part of the problem linking from a SQL 2000 to a SQL 7.0 database?
Are there any other tips out there?
Thanks.
Page Life Expectancy Is POOR
Jan 16, 2008Page Life Expectancy (PLE) is pretty bad on my server. PLE is hovering around 3 minutes "sometimes" but is usally around 20-30 seconds.
Total memory allocated to SQL ( a fixed amount ) is set at 3GB.
Of the total memory allocated, SQL Server is using 2.52GB ... so there is room if needed.
The Buffer Cache is sitting at 2.09GB with a hit rate around 99.8%.
The Procedure Cache is sitting at 378MB with a hit rate around 90.5%.
CPU is hovering around 10-20%
Free System Page Table Entries is LOW ... at 22343
Disc Queue Lengths spike quite often to above 5 and sometimes as high as 36. Usually sitting at .05 to 1.0 (and there are times when the DQLs are great and not measureable.
What I need to find out is how to get PLE above the recommended 5 minute mark???
Please let me know if there are any other items I need to note.
Thanks!
=========================================
Here are some hardware/Software/Implementation stats:
=========================================
SQL Server 2005 Standard w/ latest patch of 3152
Windows Sever 2003 R2 Standard w/ latest patches applied (says PAE is enabled in the System Properties.. General tab
4 Intel Xeon X5355 @266GHz
4GB RAM with 3GB dedicated to SQL Server via the /3GB switch
System Disc ( C: ) is 136GB (free space is 122GB) and is RAID10
Data/SQL Disc ( G: ) is 408GB (free space is 347GB) and is RAID10
The SQL files (MDFs/LDFs, TempDB, DB & TLog backups, SQL application and all that is SQL related) are all on the single array (G (** which I must note is NOT how I configured the SQL environment but aquired the setup when I started the position).
Poor Peformance On Pulling Data
Nov 12, 2007So this could be a long story.
Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM
Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram
This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross.
Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K.
Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.
Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?
Thanks in advance guys,
Regards.
Poor Sql Performance In Oledb Source?
Mar 31, 2008i need to select data by using a very complex sql statement. when i use a ole db source componente and choose SQL command as data access mode the process never ends. but when i put the sql statement in an sql task component it works fine and fast. isn't an oledb source always based on an sql statement (select *)? so how is it possible that this component becomes so slow?
View 11 Replies View RelatedPoor Performance On Sql 2005 Vs. Sql 2000 - AGAIN!
May 15, 2008I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.
I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.
1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).
2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.
3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)
4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.
I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.
SELECT InterimView.* ,TestView.*
FROM View_LabDataExport_TestFormData_55 TestView
RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.*
FROM View_LabDataExport_FormData_55 ReqView
LEFT OUTER JOIN View_LabDataExport_FormData_CD_55 CDView
ON ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID )
) InterimView
ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND
InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T )
The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list.
Here is the statistics ion on 2005
(21234 row(s) affected)
Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Here is the statistics ion on 2000
Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0.
Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0.
Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0.
Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0.
Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.
Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.
Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0.
Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0.
Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24.
Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0.
Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4.
Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10.
Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308.
Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0.
Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701.
Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16.
Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0.
Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0.
Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0.
Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2.
Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23.
Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4.
Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306.
Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047.
(21234 row(s) affected)
(147 row(s) affected)
One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask.
What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general.
Thanks
Merge Join's Poor Performance
Sep 4, 2006Hello All,
I'm experiencing performance problems with the merge join task.
Every time I'm building a nice package using this task, I'm ending up deleting it and using SQL statement in the OLE DB source to accomplish the join since it takes forever to run and crushing my computer at the process.
It makes me feel I don't use the abilities SSIS has to offer compared to DTS.
Of course for the use of several thousands of records it works fine, but in a production surrounding with hundred of thousands of rows, it seems to be futile.
Maybe someone had a little more luck with it?
SQL 2000 Poor Performance On Windows 2003
Mar 30, 2006Hi
we have a windows 2003 server with 4 xeon 3Gh and 8 gb of memory.
Now, when run a query or when click for properties is slowwwww very slow.
No error in the event view
Any idea
TIA
Abel
:angel:
Poor Performance On Dual Xeon Machine
Jul 23, 2005Hi,I've been creating a db application using MS Access and MSDE. Only twoof us are using the application, and the server and the app both rungreat on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problemis when I take my laptop home, my coworker loses access to the server.We recently purchased a dedicated server to run the db on at theoffice. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We alsobought SQL Server, but I installed the Personal Edition becuase we arenot using a server OS. It's my understanding that XP can utilize bothprocessors, and the Personal Edition can use both processors as well.(On a side note, why is Enterprise Manager showing that I have 4processors - why?) In addition, I understand PE has a work-loadgovernor that cripples performance when more than 5 TSQL commands arebeing run simultaneously.I backed up the db on my laptop and restored it on our new server. Butwhen I run the exact same queries with the exact same number of rows,my queries on the new server are take 3x longer(!?). Can someoneplease offer a few suggestions for why this is happening? What can Ido to improve performance on the server machine?Please let me know if I need to supply more information.Thanks,Alex
View 15 Replies View RelatedExtremely Poor Performance On OLE DB Command Transform.
May 1, 2006In one Data Flow Task (running by itself) I simply have a Raw File Source pushing rows to an OLE DB Command. This command executes an UPDATE command (UPDATE table SET field = ?, anotherfield = ? WHERE thisfield = ?) and performs extremely slow. It's possible to have 62K+ rows needing to be updated and it typically takes this task around 25 - 30 minutes to run.
Is there anything I can do to increase performance?
Are there any options other than the OLE DB Command to perform updates?
Thank you.
Replication Monitor Reveals Poor Performance - But What To Do To Fix It?
Jun 29, 2006The client production server CPU starts thrashing. Task manager indicates that SQL server is gobbling CPU cycles. Having a look at the replication monitor, it is obvious that indivual synchronisations to the mobile devices are taking significantly longer expected.
Observing an indivual synchronisation attempt, the Upload changes to Publisher rows are very quickly resolved.
The Download changes to Subscriber seems to take up a very long time.
Along the way, the estimated completion does a few interesting things, like going from 100% complete with no estimated time to complete, back to seomthing like 77% with 2 minutes left to complete.
This sort of behaviour occurs when there are only a hundred rows to download.
Synchronisations for minimal amounts of data suddenly taking anywhere from 2 to 15 minutes. Totally unacceptable form the client perspective but seems that 2005 behaves quite different from 2000 and the tricks are yet to reveal themselves.
Note - It is a server hardware issue as there is in excess of 3 GB ram, the database is on a SAN and there are 4 3Ghz CPUS in operation.
Any possible help appreciated as this issue is beginning to drag on.
Poor Chart Quality When Exported To Excel
Apr 2, 2008
Hi,
Whenever we export a chart to excel, the quality of the chart becomes very poor (blurred image and font).
The images look crisp when viewing from report manager and the designer.
Has anyone encountered and solved the same problem?
Thanks
Oracle Linked Server Poor Performance
Apr 16, 2007I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.
I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.
The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.
Any ideas would be appreciated
Poor Performance - Nested Views && Complex Joins
Sep 27, 2006The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt
Thanks, Dave
Poor Performance For Business Day Calculation From Aspfaq Sample
Jul 23, 2005Greeting, below is the complete SQL taken from aspfaq.com (retrievedfrom this newsgroup I believe) The query takes about two minutes torun. Does anybody have a better set based way (sub-second response) todetermine business days?CREATE TABLE dbo.Calendar(dt SMALLDATETIME NOT NULL PRIMARY KEYCLUSTERED, -- Date valueIsWeekday BIT,-- Is this date a weekday (M -F)IsHoliday BIT,-- Is this date a holidayY SMALLINT,-- Year the date falls inFY SMALLINT,-- Fiscal Year (needed?)Q TINYINT,-- Quarter date falls inM TINYINT,-- Numeric month of dateD TINYINT,-- Numeric day of dateDW TINYINT,-- Numeric DayOfWeek(Sunda=1,Monday=2)MonthName VARCHAR(9),-- String name of monthDayName VARCHAR(9),-- String name of dayW TINYINT-- Week number)GO-- Start & End DatesDECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @StartDate = DATEADD(d, -1, '20000101')SET @EndDate = DATEADD(d, -1, '20300101')-- Total number of dates to generateDECLARE @Days INTSET @Days = DATEDIFF(DAY, @StartDate, @EndDate)-- Create temporary Numbers tableCREATE TABLE #Numbers(Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED)-- Insert a number into our temp table for each date to be generatedWHILE COALESCE(SCOPE_IDENTITY(), 0) <= @DaysBEGININSERT #Numbers DEFAULT VALUESEND-- Generate a date for each day in our timespanINSERT Calendar(dt)SELECT DATEADD(DAY, Number, @StartDate)FROM #NumbersWHERE Number <= @DaysORDER BY Number-- Remove the temporary Numbers tableDROP TABLE #NumbersGO-- Update other columnsUPDATE dbo.Calendar SETIsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0ELSE 1 END,IsHoliday = 0,Y = YEAR(dt),FY = YEAR(dt),Q = CASEWHEN MONTH(dt) <= 3 THEN 1WHEN MONTH(dt) <= 6 THEN 2WHEN MONTH(dt) <= 9 THEN 3ELSE 4 END,M = MONTH(dt),D = DAY(dt),DW = DATEPART(DW, dt),MonthName = DATENAME(MONTH, dt),DayName = DATENAME(DW, dt),W = DATEPART(WK, dt)-- Query in question (takes almost 2 minutes to execute and return avalue)SELECTC.dtFROMCalendar CWHEREC.IsWeekDay = 1AND C.IsHoliday = 0AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )
View 18 Replies View RelatedSQL Server 2005 Express Edition, Very Poor Performance
Nov 14, 2007
Hi,
I have upsized my access database to Mssql 2005 and I notice that SQL 2005 is much slower in data access than the access databse.
Is there something that needs to be done after the upsize? Have I missed something?
If you want to compare the dataabses here are the links:
Access: http://ozeldersverenler.net/beta/www
SQL2005: http://ozeldersverenler.net/alpha/www
Poor Performance Temporarily Cured By Re-booting SQL Server
Feb 27, 2007Poor Performance Temporarily Cured by Re-booting SQL Server
Please can you help?
SQL Server 2000.
Accessed by VB.Net 2003.
Server set up by SQL consultant (who is no longer available).
We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).
The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.
Additional information:
Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.