.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

ORDER BY extremely slow in SQL 2008 R2

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

We have been running our databases on Server 2003/SQL 2005, and recently set up a new server running Server 2008 R2/SQL 2008 R2. For the most part, performance has been just fine. However, there is a particular query that runs quickly (<1 sec) on 2005, but very slowly (over 30 minutes!) on our new setup. The query is:

 SELECT CT.ClearingUniqueTradeID, TradeID From Polaris.Bridge.TradeClearingTrade TCT
 INNER JOIN [Polaris].[Clearing].[ClearingTrade] CT ON CT.ClearingTradeID = TCT.ClearingTradeID
 WHERE TradeDate = '2010-06-11'
 AND ClearingTradeStatusID = 4
 ORDER BY ClearingUniqueTradeID

The query returns about 20,000 rows. If we remove the ORDER BY, it runs in less than a second. I've run the Tuning Advisor against the query and it suggested a couple of indices - these make the query fast again, but I don't think that should be necessary merely to sort 20k rows of numerical values. SQL 2005 did fine without them.

I did apply CU4 to see if there were a

View Complete Post

More Related Resource Links

Datagridview painting "EXTREMELY SLOW" on new PC

Hi, I have a appliation I wrote in .net 2.0 (VS 2005) and on all PCs except one it runs great, but on this one laptop (a brand new Dell XPS 1530) the Datagridview paints so slow when scrolling or resizing that you can see each row paint as if in slow motion.  It's so bad I would be extremely hesitant to relase the application as I know someone would complain.   Has anyone else seen this issue?  It's only happening on certain PCs.  The PCs in question are all up to date via windows update, so they have the same versions of everything.  The laptop has a Nvidia 8600GT chip in it with the latest drivers and I actually have the same drivers on some of the PCs where the grid is fast.   Thanks,   Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com

BIDS Query Designer very slow in VS 2008

I am in the process of upgrading my Report Server projects from 2005 to 2008.  These reports use an Analysis Services 2005 data source and have queries that are anywhere from 20 to 100 lines of MDX.  After upgrading a report, when I open or save a dataset in the Query Designer, it takes up to 20 seconds.  In 2005, this would happen almost instantly. Running SQL Server Profiler, I see that there is an extensive number (600+) of Discover events going on with the Analysis Services server while the Query Designer loads. My version of Visual Studio is 9.0.30729.1 SP and I am running Windows XP SP2. Thanks, Mitch

Extremely Slow Page Loads

Thought I would reach out to the experts and see if anybody might be able to point me in a direction as I do not know where to turn next. Any and all help is greatly appreciated. All of a sudden page load times on the primary web app in my SP2007 farm are extremely slow. Making the site almost unusable. Network performance seems fine as does SQL -- no obvious errors in the logs. The problem happens intermittently. Environment: •  Win2k3 SP2 servers    •  1 WFE (VM)        •  SharePoint 2007 SP2 w/ JUN 2010 cumulative update installed.        •  Most recent WU patches applied about 10 days ago (suspected cause?)      •  1 SQL 2005 (metal)        •  The content database is 20.7 GB, so it fall withing BPs.         •  My DBA has said the DBs do not look overly fragnmented •  1 site collection •  4 web apps •  The VM environment is VMWare vSphere. Search indexing (full) is disabled; All app pools have been recycled; Warmup scripts have been run; and the server has been been rebooted. Have anyone run across anything like this before? Can you point me in a direction? Thanks in advance.

Scrolling through lists extremely slow

Hi , When viewing lists in SharePoint 2010 it's abnormally sluggish when scrolling especially with the scroll wheel on the mouse. If you grab the scroll bar with your mouse its abit faster but when scrolling with the wheel it jumps up and down. Viewing a list with 500 items on a page (which worked perfectly without issue in MOSS) is unbareable. Also when trying to edit the page that this list view is on, it takes for ever to load. Seems like a javascript issue, has anybody else experienced the same issue and know how to fix it? Note: the list with 500 items has been migrated from MOSS by upgrading a MOSS 2007 DB if that makes a difference. I have tested with a brand new list and the issue still occurs

SQL Server 2008 R2 deploying any version of sharepoint results in extremly slow DB creation

Hello, When installing any version of Sharepoint(2007,2010) during the "Creating Configuration Database" Step it takes over 10 hours to complete.  Then creating a web app takes just as long. and the sites are extreemply slow.  Environment: 1) DB Server - SQL Server 2008 R2 on Windows Server 2008 R2 2) Webserver - to host Sharepoint 2010 on Windows Server 2008 R2 The Servers are on the same network and running with lots of horsepower, disk and memory.  Are there any know issues with this and should we install Cummulative Update 3? Maybe that has a fix.  We exhausted all our options and need justification to apply the CU3 hotfix(s). Thanks for your help.

Extremely slow Postback or Redirect

I have been coding simple ASP.NET pages for quite a while, but this problem is stumping me. I have a page that is almost purely HTML except for a few ASP.NET buttons and labels - as you can see in the code below. Problem: When I click on a button (that is not set up to do anything) or hit refresh, the page reloads twice at a normal rate and then the third time (and everytime therafter) it takes about 40 seconds to reload. On the first two reloads, some of the images don't always show up (blank placeholders) and sometimes they show a broken link placeholder. If I refresh the page too many times (10 or so) I get: HTTP 403.9 - Access Forbidden: Too many users are connected Internet Information Services When I click on a button that has a response.redirect, it takes 40 seconds to load the page even if it is the first button I have clicked. One of the buttons points to a page that I have not yet created and clicking that button takes 40 seconds to tell me the resource does not exits. Platform: The webpage is running on a Win XP Pro, box with 2GHz, 256RAM and IIS6 and SP2. On the web.config file all I have done is turn off custom errors and turned off debug. I get the same result hitting the page from the webpage server or from different machines. I am the only person hitting the website. I tested putting the page on a different server (XP Server 2003 box

SSRS 2008 Against OLAP Cube reports rendering slow in IE 6 and IE8

We have SSAS cube built on SQL Server 2008 that has multiple parameters that have a default vault on running of all. The first time we run the report it renders the report in less than 30 seconds, but when we change one value on the data cube pull the reports can take up to 5 mins to run. If I do the same report in Firefox 3.6 it renders the report in 30 seconds. I have change changing the cache size in the IE explorer and ave the latest .net frame work on the computer. What would be causing this issue? The company upgrade from SSRS, SSIS, SSAS, SQL 2005 to 2008. Please advice, this is very annoying for our business end users.   Thanks for your help in advance.

WPF excruciatingly slow in Visual Studio 2008

And apparently single threaded, as only one core is maxing out.  Are there any performance tricks to speed this up?  When I'm editing the xaml, I'm sometimes waiting in excess of a minute to get cursor control back.  This seems to be particularly bad when using a wpf control library.  Any help?  Anyone else having the same issue?

Slow sql-server after upgrade from SQL-Server 2005 x64 standard to SQL-Server 2008 R2 x64 enterprise


We moved our databases to new hardware today (using copy database wizard)


The new hardware should be better than the old one.

We went from 28 GB ram to 128 GB.

We went from 16 cores to 32 cores (with even more cache etc.). We also moved from external SAN with 40 disks to intern disks (8 Samsung SS805 100gb ssd slc)

                             According to our own metrics we could get twice the IO using IO m

Why call web serivce so slow on windows2003 sp2(or windows server 2008) iis6?


my web service reference a ATL COM dll(provide many interface to access MS Sql server).

i create a ATL Activex Control call the web service.

when i test IIS server, the problem appears:

1 when i use win2003 (no server packs). the activex call the web serivce fast, less than 1s.

2 when i use win2003 (sp1).   the activex call the web serivce fast less than 1s.

3 when i use win2003 (sp2).   the activex call the web serivce slow, test the same function will cost more than 3s.

4 when i use win2008. got the result same as 3.

Why ? Can anyone help me?


thank u all!
Chinese fairy tale

2005 query runs slow in 2008



I'm running some of our stored  procedures, written on 2005, on a test 2008 server

One of the stored procedures runs much slower on 2008, the execution plan is considerably different as well.

I've tried changing the compatability level, to no effect.

I'm wondering f there are any general actions I can take to mitigate this issue, or whether I'm just going to have to deal with these problems on a one by one basis.





Report exported to Excel 2007 is EXTREMELY slow once a filter is applied.


I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.

SQL Server 2008 client very slow on new Win7 PC


I installed SQL Server 2008 Management Studio on a brand new PC (HP ProBook 6540b with an i5 CPU and 2GB RAM) running Windows 7 x86. A script that runs some stored procedures on a Windows 2008 x64 server (also SQL 2008) and which returns about 10 result sets takes a fraction of a second on our old PC's, including one that runs Windows 7 (the rest are XP and Vista). But on this PC, it takes 30 seconds! I installed the client tools the same way as always, and applied SP1. I disabled the Windows Firewall and anti virus, and then disabled the Aero interface, and it is still slow (now about 15 seconds). Non-SQL functions are faster, e.g. copying files over the network is almost 3x faster than on my old PC, so there doesn't seem to be a general speed problem with the PC.

Do I need to change any settings for Windows 7? I ran the configuration manager, but the settings look the same as on the old PC. I disabled shared memory (I think this is just for a local database server?), but this made no difference.

Any ideas? Thanks!



Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration



We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.



The Application is performaing very slow after upgrade to SQL Server 2008 x64


Hello All,

I am writing you all after I tried a lot of solutions to resolve my Issue, please find below my issue details..

Task: Upgrade from SQL Server 2000 Ent. Edition to SQL Server 2008 X64 for IA on windows server 2008 Clustered environment (Active\Active)

SQL Server 2008 Servers HW configuration:

  • 24 GB Of Ram
  • 2x Process or dual core
  • SAN storage
  • up to 1 Tera Storage

Visual Studio 2008 ASP.NET Builds VERY Slow


Whenever I debug an ASP.NET page in VS2008 (no matter how simple the page is), it hangs for at least 90 seconds.  The "build started > build succeeded" bit only takes three or four seconds...then it hangs for what seems like an eternity.  Periodically, "Loading symbols xxxxxxxxxxx" will flash at the bottom, and finally after about 90 seconds, it'll hit the Page_Load event.

 Any idea as to what's going on?


Thanks in advance.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend