Showing posts with label standard. Show all posts
Showing posts with label standard. Show all posts

Monday, March 12, 2012

Experts only: SQL Server does not exist or access denied.

Hi,
I'm accessing MS SQL server standard edition using ASP.net 1.1 web applications
my site has traffic ranking 84,000 on alexa.com. all web applicationsare mounted on One sigle Application pool. I have developed
monitoring webservice for monitoring the runtime errors occured duringtransactions, where I came across below errors all the time. duringheavy traffic.
1. SQL Server does not exist or access denied.
2. General network error. Check your network documentation.
3. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
First one is very important as Microsoft it self agree that it was bug in their products. How can I fix it.

http://support.microsoft.com/kb/328306

Client uses TCP/IP and Named Pipes.

I'm hoping to get it fixed using this thread.

Thanks,
Satalaj.

Have you checked the server loads on the machine that is running SQL during peak times?

I'm far from an expert on this subject, but it seems the server can't respond due to server load. How many concurrent users do you have during peaks?

Cheers!

/Eskil

|||

Yes, It is happening during peak load. How can I fix it.

Server DB size is 3GB and RAM 1GB. server uses 60% to 99% of RAM during peak load.

|||

Did you check the Eventviewer to see if your SQL Server Service is getting restarted/recycled during this peak loads that makes it unavailable for a few seconds/minutes.

|||

Hi urstop

If this is the case then all my webapplications should produce same erro, It is only happening for someweb applications mounted on one application domain.

|||

But are your other web applications also have lot of traffic. Because if the traffic is less for other applications then there are chances that during this error period we do not have any traffic coming to your other web applications. Just a thought.

|||I have written a webservice whichkeeps log of all error it gets called from webapplication if any erroroccured in web applications. Do you know how can I stop recycling ofthe SQL server. or Do I need to update the RAM on SERVER. if so howmuch RAM will be required for DB of size 3GB|||

You have mentioned that you have 1GB of RAM. I think that should be good enough for your DB size.

Also, does your eventviewer has events which indicate that SQL Service is getting recycled? Becuase it should not be a case where we are looking in a wrong areaSmile

|||

RAM/CPU limitations on your SQL server will usually result in slow queries during peak times and not error messages. Take a look at your page file usage and memory available to see if this might be the case. I would definately suggest adding a gb, 2gb should be plenty but extra never hurts.

Is the web server separate from the SQL server or are they on the same machine? If they are on the same separating the two might help as well.

|||

Hi Dustin, web and SQl server are separated. I'm going to see the log as per urstop suggession

Friday, March 9, 2012

expectations for SUM query on 50+ million rows

what's a reasonable expectation for a query like this run against a
table with 50+ million rows with SQL 2005 Standard:
SELECT
NAME, SUM(Tickets) as NumTickets
FROM
MYTABLE
WHERE
TicketDate BETWEEN @.StartDate and @.EndDate
GROUP BY
NAME
ORDER BY
NumTickets DESC
let's assume my table is properly indexed, and the server machine is a
dual xeon 2.8 with 2GB of memory, and plain jane SATA drives in RAID-1.
also assume the result rows (between start and enddate) could be
anywhere from 5k to 500k rows, and there's minimal load on the server.
any takers? I'm looking for a ballpark of how well SQL should be able
to handle this...and things to consider to speed this query up.
thanks!do you need sum or count?
take a look at this count is 2 but sum is 3
select count(tickets),sum(tickets)
from (select 1 as tickets union all
select 2) x
http://sqlservercode.blogspot.com/|||"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1142527278.244991.109940@.e56g2000cwe.googlegroups.com...
> what's a reasonable expectation for a query like this run against a
> table with 50+ million rows with SQL 2005 Standard:
> SELECT
> NAME, SUM(Tickets) as NumTickets
> FROM
> MYTABLE
> WHERE
> TicketDate BETWEEN @.StartDate and @.EndDate
> GROUP BY
> NAME
> ORDER BY
> NumTickets DESC
> let's assume my table is properly indexed, and the server machine is a
> dual xeon 2.8 with 2GB of memory, and plain jane SATA drives in RAID-1.
> also assume the result rows (between start and enddate) could be
> anywhere from 5k to 500k rows, and there's minimal load on the server.
> any takers? I'm looking for a ballpark of how well SQL should be able
> to handle this...and things to consider to speed this query up.
>
Test it.
David|||how many dates are there?
is there an index on ticketdate?
is there a composite, non-clustered index on ticketdate,name,tickets ?
i'd try changing the where clause to
WHERE
TicketDate >= @.StartDate and
TicketDate <= @.EndDate
Just a personal deal - sometimes between isn't quite obnoxious enough
on the hint to the compiler.
how many bytes is the average row? if the average row is 1k, then
gonna take a long time. if average row is 50 bytes, then i'll say 1
second per 10k rows, given the perfect index.|||On 16 Mar 2006 08:41:18 -0800, Jeff Turner wrote:
>what's a reasonable expectation for a query like this run against a
>table with 50+ million rows with SQL 2005 Standard:
(snip query and hardware description)
Hi Jeff,
I have no idea - you'll have to test it.
So why do I bother to reply? Becuase you also ask for ...
>...and things to consider to speed this query up.
Try the following two indexes. Hard to predict which one of them will be
the best.
CREATE NONCLUSTERED INDEX SomeName
ON MyTable (Name, TicketDate, Tickets)
*** or ***
CREATE NONCLUSTERED INDEX SomeName
ON MyTable (TicketDate, Name, Tickets)
Hugo Kornelis, SQL Server MVP