Performance goes down

We're an ISP using SQL 6.5. When the user connects to us, the RADIUS server
queries the database to authenticate the user. This is happening all the
time. However when I run a query against the database that effects a large
no. of rows and runs for a minute or two, often the authentication process
stops and
we start getting calls from our customers complaining they cant connect. We
then have to start and stop the RADIUS service and SQL to enable the process
again.
Any ideas to get around this problem are appreciated.
thxCould it be the SQL Server is hogging all the resources when you submit
these queries & subsequently putting users on hold?
Have you run perfmon to checkout what is happening when you execute these
queries, & in particular the difference in utilisation between this & the
normal baseline utilisation?
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:c39apn$2370k1$1@.ID-31123.news.uni-berlin.de...
> We're an ISP using SQL 6.5. When the user connects to us, the RADIUS
server
> queries the database to authenticate the user. This is happening all the
> time. However when I run a query against the database that effects a large
> no. of rows and runs for a minute or two, often the authentication process
> stops and
> we start getting calls from our customers complaining they cant connect.
We
> then have to start and stop the RADIUS service and SQL to enable the
process
> again.
> Any ideas to get around this problem are appreciated.
> thx
>
>
>|||As the previous poster says, use Perfmon to take a look at proc, memory and
disk utilization during these times.
If you have a multi proc machine and the proc is the issue,. you may tell
SQL to NOT use all of the processors, leaving at least proc to handle the
Radius work.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:c39apn$2370k1$1@.ID-31123.news.uni-berlin.de...
> We're an ISP using SQL 6.5. When the user connects to us, the RADIUS
server
> queries the database to authenticate the user. This is happening all the
> time. However when I run a query against the database that effects a large
> no. of rows and runs for a minute or two, often the authentication process
> stops and
> we start getting calls from our customers complaining they cant connect.
We
> then have to start and stop the RADIUS service and SQL to enable the
process
> again.
> Any ideas to get around this problem are appreciated.
> thx
>
>
>|||Mansoor Azam wrote:
> We're an ISP using SQL 6.5. When the user connects to us, the RADIUS serve
r
> queries the database to authenticate the user. This is happening all the
> time.
Sounds like what is happening is the query you are running is holding
exclusive locks on the rows that are needed for the radius authentication.
To alleviate this you will want to have radius authenticate from a
static table that has is not used for any reporting purposes. Typically
what I do is create a trigger on the table in the billing system and
whenever a row is modified I replicate that to another isolated table.
This isolated table is what is used by radius. If you have the option,
you will also want to try and use stored procedures and in them make
your query as specific as possible.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:<c39apn$2370k1$1@.ID-31123.news.uni
-berlin.de>...
> We're an ISP using SQL 6.5. When the user connects to us, the RADIUS serve
r
> queries the database to authenticate the user. This is happening all the
> time. However when I run a query against the database that effects a large
> no. of rows and runs for a minute or two, often the authentication process
> stops and
> we start getting calls from our customers complaining they cant connect. W
e
> then have to start and stop the RADIUS service and SQL to enable the proce
ss
> again.
> Any ideas to get around this problem are appreciated.
> thx
As a complete guess, your long-running query is locking the table(s)
required for users to authenticate, so the authentication process
times out. You could try using sp_lock to see if this is the case. But
in general, running a long query against a production database while
users are working isn't a good idea - if it's for reporting or
something similar, maybe you could use a separate copy of the database
to avoid affecting the users? Although since I don't know exactly what
your situation is, that may not be possible for you.
Simon|||[posted and mailed, please reply in news]
Mansoor Azam (mansoorb@.shoa.net) writes:
> We're an ISP using SQL 6.5. When the user connects to us, the RADIUS
> server queries the database to authenticate the user. This is happening
> all the time. However when I run a query against the database that
> effects a large no. of rows and runs for a minute or two, often the
> authentication process stops and we start getting calls from our
> customers complaining they cant connect. We then have to start and stop
> the RADIUS service and SQL to enable the process again.
> Any ideas to get around this problem are appreciated.
As others have pointed out, this is likely to be a locking issue. If
the authentication writes information somewhere, for instance in a userlog,
and your query affects that table, you block logins.
One way to avoid this problem is to include (NOLOCK) after all tables
in the query. When you will read the tables without using any locks.
This means that your query may not return consistent data. But that
is probably better than users not being able to log on!
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql