Hi All,
I have a client using SQL Server 7.0 Sp3, and they have
clients connecting to it via ODBC (32) bit driver, some of
them are receiving a error message of communication Link
failure. They have MDAC 2.7.1 installed, they tried using
TCP/IP, and Name pipes, the error keeps happening.
If you have any ideas it would greatly be appreciated.
Thanks
ScottCan you ping the sql server when they receive the error?
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Wednesday, March 28, 2012
Receive notification on several clients at the same time
Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.I think the problem is when your client polls the Q and does a RECEIVE, the
message is lost, thus if another client is also looking for the same
message, it will not be there.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
> Hello.
> I'd like to reveive notifications on the client application when table
> data is changed.
> How could I use Service Broker, queues and services? In my tests I
> have test queue (NotifyTestQueue), test service (NotifyTestService).
> If only one client application instance is running - all works fine.
> Application runs sql query
> WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
> in the separate thread and thread execution is continued after message
> in the queue is available.
> But if I run two ore more client application instance only one of them
> receives notification :(
> Help me please - how could I set up service broker or queue or service
> to enalbe notification for several client about the same event in the
> queue?
> Should I give up and use individual table with records as events for
> clients?
> Thank you.
>|||Consider using Notification Services.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.|||The easiest thing is to use QueryNotification which is one of the new
features within SQL Server 2005.
HTH, jens Suessmeyer.|||Hello Jens.
I've use query notification but it works only for last client. Here is
the code:
So after key is pressed and event is fired on the server only one
client gots event.|||I should explain what is happened with the code above.
So when one client executes NotificationTest method all works fine.
Client gets notification from the SQL server.
But when two or more client were started only one (last started)
receives notification - all others don't. As SriSamp says one client
application polls the queue and other clients miss event. How could I
leave message in the queue when RECEIVE statement is executed? I cann't
find anything that may help me with it :(
Thank you.|||A message can only be dequeued once. There's no way around this.
What you want to do is to multiply that one message into N messages, for the
N clients. Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN. Create activated
stored procedure on the NotifyTestQueue that when it receives a message it
forwards this message to all clients (by SENDing the message N times). This
is basically a pub-sub system implemented on top of dialogs. A practical
example on how to do this is at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification, each
of these statements will block one thread on the server, and you'll hit the
upper configured limit max_worker_threads
http://msdn.microsoft.com/library/d... />
g_09wu.asp
If you need a large number of clients the solution must involve partitioning
out the N client services/queues on a number of different SQL Instances,
since SSB can deliver the notification remotely.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140536881.331233.150770@.f14g2000cwb.googlegroups.com...
>I should explain what is happened with the code above.
> So when one client executes NotificationTest method all works fine.
> Client gets notification from the SQL server.
> But when two or more client were started only one (last started)
> receives notification - all others don't. As SriSamp says one client
> application polls the queue and other clients miss event. How could I
> leave message in the queue when RECEIVE statement is executed? I cann't
> find anything that may help me with it :(
> Thank you.
>|||Hello Remus.
Thank you for fast reply.
Can I use RETENTION = ON parameter on queue?
> Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN.
Its not suitable for me - I don't know how many clients will be
connected to the database at future.
> Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
each
of these statements will block one thread on the serve
I'd like to perform RECEIVE without WAITFOR from time to time in the
separate thread on the client.
It seems to me that there is still only one method to notify connected
clients that changes in data occured. Fill some special notify table
and polls this table from time to time at the client. Is it true?
Thank you.|||See inline
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140594793.299405.111750@.g44g2000cwa.googlegroups.com...
> Hello Remus.
> Thank you for fast reply.
> Can I use RETENTION = ON parameter on queue?
Remus: No, retention does not allow for a message to be receive more than
one time. Retention keeps a copy of the message in the queue if compensation
is needed, but this copy is not receivable.
> waits on a dedicated queue, NotifyClient1...NoifyClientN.
> Its not suitable for me - I don't know how many clients will be
> connected to the database at future.
Remus: That's manageable, clients can create a queue and then
subscribe/unsubscribe. Separate queues allow for clients to fan out and be
partitioned on several machines for scalability.
>
> each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
> each
> of these statements will block one thread on the serve
> I'd like to perform RECEIVE without WAITFOR from time to time in the
> separate thread on the client.
Remus: Any reason for this? Pooling is in general worse than WAITFOR. Note
that
WAITFOR (RECEIVE) does not pool, it uses a notification mechanism.
> It seems to me that there is still only one method to notify connected
> clients that changes in data occured. Fill some special notify table
> and polls this table from time to time at the client. Is it true?
Remus: No, this is not true. Pooling for changes or pooling for change
notifications is limited in scalability. There is the potential to overload
the server with just answering the queries that are pooling for changes. The
way to address this is to fan out change notifications for each subscribed
client.
You can actually decide which approach is better, based on the specifics of
you case. Some back of the envelope cacluclation will give you a rough
answer:
First, lets figure out the pooling case. Consider you have N clients, each
pooling at an interval of t seconds and the pooling query batch consumes r
seconds on the server. On any interval of time T you are gonna consume a
total pooling runtime on the server R = r x t/N x T . The R/T ratio gives
how much of the total server runtime is consumed serving the pooling
queries. So basically the r x t/N number is giving you this ratio.
In the case of WAITFOR (RECEIVE), you're gonna have N clients blocked on the
server waiting for notifications. Considering each individual client is
going to be notified (per second) with a probability p and the server
runtime consumed for seting up the WAITFOR, delivering the notification and
returning the result is w, then the total runtime consumed on the server
during the interval T will be w x p x T x N. Again, dividing this by T gives
you the ratio of server runtime consumed in servicing these notifications.
So basically the ratio is w x p x N.
So in the end you're left comparing the r x t/N versus w x p x N.
If you consider the pool query runtime r as being based on a SELECT over one
clustered index and the WAITFOR w time as a RECEIVE from a queue, a rough
approximation of w vs. r should be w = 3 x r (maybe 4 x r), because the
WAITFOR will execute a similar scan as the SELECT, but twice (once before
suspending itself, once when is notified) and it also executes a write
operation (the dequeue)
So is all about comparing t/N vs. 3 x p x N.
- smaller t means clients cannot tolerate dirty cache, they need to pool
frequently, larger t means clients are OK with presenting potentially
outdated data. A large t favors the pooling model.
- larger p means the data is updated often and the cache will be invalidated
often for each client, smaller p means the cache invalidation is unfrequent.
A small p favors the WAITFOR.
Is also important if there is a corelation between p and N. If
each client is interested in a separate partition of the possible data
changes, then increasing N will likely decrease p, favouring the WAITFOR (or
at least hurting it less than the pooling). If all clients are interested in
same changes p does not decrease with an increase of N, this case hurst both
models.
Also, given a sufficient large N both models will crumble as the ratio
approaches 1, meaning all of the server resources are required just to
service the notifications. This case must reside to a fan-out mechanism the
WAITFOR model is better suited for.
> Thank you.
>|||Hello.
I cann't ever express how you post helps me. Thank you very much.
I should read more about Notification services and how it could be
connected with Service borker.
Good luck.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.I think the problem is when your client polls the Q and does a RECEIVE, the
message is lost, thus if another client is also looking for the same
message, it will not be there.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
> Hello.
> I'd like to reveive notifications on the client application when table
> data is changed.
> How could I use Service Broker, queues and services? In my tests I
> have test queue (NotifyTestQueue), test service (NotifyTestService).
> If only one client application instance is running - all works fine.
> Application runs sql query
> WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
> in the separate thread and thread execution is continued after message
> in the queue is available.
> But if I run two ore more client application instance only one of them
> receives notification :(
> Help me please - how could I set up service broker or queue or service
> to enalbe notification for several client about the same event in the
> queue?
> Should I give up and use individual table with records as events for
> clients?
> Thank you.
>|||Consider using Notification Services.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.|||The easiest thing is to use QueryNotification which is one of the new
features within SQL Server 2005.
HTH, jens Suessmeyer.|||Hello Jens.
I've use query notification but it works only for last client. Here is
the code:
code:
private static void NotificationTest()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"SELECT GenderID, IsActive, DescrLocal FROM dbo.cls_Genders",
connection);
SqlNotificationRequest notifyRequest = new SqlNotificationRequest();
notifyRequest.UserData = "Any User Data";
notifyRequest.Options = "service=NotifyTestService";
notifyRequest.Timeout = 600;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine(rdr[0] + "\t" + rdr[2] + "\t" + rdr[1]);
}
Console.WriteLine("notifyRequest was sent");
Console.ReadKey();
WaitForChanges();
}
private static void WaitForChanges()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)",
connection);
cmd.CommandTimeout = 0;
object o = cmd.ExecuteScalar();
Console.WriteLine(o);
}
}
So after key is pressed and event is fired on the server only one
client gots event.|||I should explain what is happened with the code above.
So when one client executes NotificationTest method all works fine.
Client gets notification from the SQL server.
But when two or more client were started only one (last started)
receives notification - all others don't. As SriSamp says one client
application polls the queue and other clients miss event. How could I
leave message in the queue when RECEIVE statement is executed? I cann't
find anything that may help me with it :(
Thank you.|||A message can only be dequeued once. There's no way around this.
What you want to do is to multiply that one message into N messages, for the
N clients. Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN. Create activated
stored procedure on the NotifyTestQueue that when it receives a message it
forwards this message to all clients (by SENDing the message N times). This
is basically a pub-sub system implemented on top of dialogs. A practical
example on how to do this is at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification, each
of these statements will block one thread on the server, and you'll hit the
upper configured limit max_worker_threads
http://msdn.microsoft.com/library/d... />
g_09wu.asp
If you need a large number of clients the solution must involve partitioning
out the N client services/queues on a number of different SQL Instances,
since SSB can deliver the notification remotely.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140536881.331233.150770@.f14g2000cwb.googlegroups.com...
>I should explain what is happened with the code above.
> So when one client executes NotificationTest method all works fine.
> Client gets notification from the SQL server.
> But when two or more client were started only one (last started)
> receives notification - all others don't. As SriSamp says one client
> application polls the queue and other clients miss event. How could I
> leave message in the queue when RECEIVE statement is executed? I cann't
> find anything that may help me with it :(
> Thank you.
>|||Hello Remus.
Thank you for fast reply.
Can I use RETENTION = ON parameter on queue?
> Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN.
Its not suitable for me - I don't know how many clients will be
connected to the database at future.
> Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
each
of these statements will block one thread on the serve
I'd like to perform RECEIVE without WAITFOR from time to time in the
separate thread on the client.
It seems to me that there is still only one method to notify connected
clients that changes in data occured. Fill some special notify table
and polls this table from time to time at the client. Is it true?
Thank you.|||See inline
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140594793.299405.111750@.g44g2000cwa.googlegroups.com...
> Hello Remus.
> Thank you for fast reply.
> Can I use RETENTION = ON parameter on queue?
Remus: No, retention does not allow for a message to be receive more than
one time. Retention keeps a copy of the message in the queue if compensation
is needed, but this copy is not receivable.
> waits on a dedicated queue, NotifyClient1...NoifyClientN.
> Its not suitable for me - I don't know how many clients will be
> connected to the database at future.
Remus: That's manageable, clients can create a queue and then
subscribe/unsubscribe. Separate queues allow for clients to fan out and be
partitioned on several machines for scalability.
>
> each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
> each
> of these statements will block one thread on the serve
> I'd like to perform RECEIVE without WAITFOR from time to time in the
> separate thread on the client.
Remus: Any reason for this? Pooling is in general worse than WAITFOR. Note
that
WAITFOR (RECEIVE) does not pool, it uses a notification mechanism.
> It seems to me that there is still only one method to notify connected
> clients that changes in data occured. Fill some special notify table
> and polls this table from time to time at the client. Is it true?
Remus: No, this is not true. Pooling for changes or pooling for change
notifications is limited in scalability. There is the potential to overload
the server with just answering the queries that are pooling for changes. The
way to address this is to fan out change notifications for each subscribed
client.
You can actually decide which approach is better, based on the specifics of
you case. Some back of the envelope cacluclation will give you a rough
answer:
First, lets figure out the pooling case. Consider you have N clients, each
pooling at an interval of t seconds and the pooling query batch consumes r
seconds on the server. On any interval of time T you are gonna consume a
total pooling runtime on the server R = r x t/N x T . The R/T ratio gives
how much of the total server runtime is consumed serving the pooling
queries. So basically the r x t/N number is giving you this ratio.
In the case of WAITFOR (RECEIVE), you're gonna have N clients blocked on the
server waiting for notifications. Considering each individual client is
going to be notified (per second) with a probability p and the server
runtime consumed for seting up the WAITFOR, delivering the notification and
returning the result is w, then the total runtime consumed on the server
during the interval T will be w x p x T x N. Again, dividing this by T gives
you the ratio of server runtime consumed in servicing these notifications.
So basically the ratio is w x p x N.
So in the end you're left comparing the r x t/N versus w x p x N.
If you consider the pool query runtime r as being based on a SELECT over one
clustered index and the WAITFOR w time as a RECEIVE from a queue, a rough
approximation of w vs. r should be w = 3 x r (maybe 4 x r), because the
WAITFOR will execute a similar scan as the SELECT, but twice (once before
suspending itself, once when is notified) and it also executes a write
operation (the dequeue)
So is all about comparing t/N vs. 3 x p x N.
- smaller t means clients cannot tolerate dirty cache, they need to pool
frequently, larger t means clients are OK with presenting potentially
outdated data. A large t favors the pooling model.
- larger p means the data is updated often and the cache will be invalidated
often for each client, smaller p means the cache invalidation is unfrequent.
A small p favors the WAITFOR.
Is also important if there is a corelation between p and N. If
each client is interested in a separate partition of the possible data
changes, then increasing N will likely decrease p, favouring the WAITFOR (or
at least hurting it less than the pooling). If all clients are interested in
same changes p does not decrease with an increase of N, this case hurst both
models.
Also, given a sufficient large N both models will crumble as the ratio
approaches 1, meaning all of the server resources are required just to
service the notifications. This case must reside to a fan-out mechanism the
WAITFOR model is better suited for.
> Thank you.
>|||Hello.
I cann't ever express how you post helps me. Thank you very much.
I should read more about Notification services and how it could be
connected with Service borker.
Good luck.
Receive Error: 17805 Invalid buffer received from client
Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error?
Please advise..
Cheers!
BrettSee response in microsoft.public.sqlserver.clients
-Sue
On Fri, 21 Oct 2005 13:50:53 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:
>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour
I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
>State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i nee
d
>to specify in the trace to identify the transaction that is causing this
>error?
>Please advise..
>Cheers!
>Brett
>
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error?
Please advise..
Cheers!
BrettSee response in microsoft.public.sqlserver.clients
-Sue
On Fri, 21 Oct 2005 13:50:53 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:
>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour
I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
>State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i nee
d
>to specify in the trace to identify the transaction that is causing this
>error?
>Please advise..
>Cheers!
>Brett
>
Receive Error: 17805 Invalid buffer received from client
Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error and what kind of filter if any do i need to apply?
Please advise..
Cheers!
Brett
You should be able to track it down quickly if it's
happening every hour. You get capture exceptions using the
Errors and Warnings class. You can then see the spid that
generated this error. If you also capture the T-SQL
statements and stored procedures at the same time, you will
see what the spid that generated the exception was
executing.
What you filter on depends more on your system. You can at
least filter out the applications SQL Profiler and SQLAgent%
-Sue
On Fri, 21 Oct 2005 15:33:20 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:
>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
> State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error and what kind of filter if any do i need to apply?
>Please advise..
>Cheers!
>Brett
>
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error and what kind of filter if any do i need to apply?
Please advise..
Cheers!
Brett
You should be able to track it down quickly if it's
happening every hour. You get capture exceptions using the
Errors and Warnings class. You can then see the spid that
generated this error. If you also capture the T-SQL
statements and stored procedures at the same time, you will
see what the spid that generated the exception was
executing.
What you filter on depends more on your system. You can at
least filter out the applications SQL Profiler and SQLAgent%
-Sue
On Fri, 21 Oct 2005 15:33:20 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:
>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
> State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error and what kind of filter if any do i need to apply?
>Please advise..
>Cheers!
>Brett
>
Receive Error: 17805 Invalid buffer received from client
Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error?
Please advise..
Cheers!
Brett
See response in microsoft.public.sqlserver.clients
-Sue
On Fri, 21 Oct 2005 13:50:53 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:
>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
>State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error?
>Please advise..
>Cheers!
>Brett
>
sql
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error?
Please advise..
Cheers!
Brett
See response in microsoft.public.sqlserver.clients
-Sue
On Fri, 21 Oct 2005 13:50:53 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:
>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
>State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error?
>Please advise..
>Cheers!
>Brett
>
sql
Monday, March 26, 2012
Rebuilding the system merge repl indexes
Hi,
We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.
Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?
Thanks for your help
Graham
1. What is the retention period?
2. Was the cleanup job run?
|||The retention period is 14 days for all subscribers.
The Agent history clean uo: Distributor job (publisher and distributor are on the same box), was last run yesterday successfully.
The Distribution Clean up: Distributor job has never been run and it not enabled.
The Replication monitoring refresher for Distributor job has never been run and is not enabled.
|||1. Did the replication Monitor running all the time (on the subscriber)?
2. If you stop and re-start to launch it, does it still take a long time to refresh "sync status"?
3. if the repl monitor still takes a long time to refresh the sync status, can you turn on the profiler to see which RPC call takes unexpected long execution time? (I suspect the SQL Agent job history continuous to grow)
This posting is provided "AS IS" with no warranties, and confers no rights
|||Replication does not do any index rebuilding or any defragmentation. It may be a good idea to create a job that does this for you during off-peak hours.
You may also want to monitor what's going on in the background while you're refreshing, replmonitor does make use of temp tables as well and it may have some contention with existing replication metadata tables that we're trying to improve for the next release of Katmai.
|||Things took at turn for the worse yesterday. I had to reinitialize one of the subscribers because for some reason the publisher had decided to delete its merge meta data (the thing that normally happens when the subscriber does not sync for more than the retention period) dispite it sync'ing the day before. When I did the reinit, it then told me the snapshot was obsolete and I had to re-generate it. When I re-generated the snapshot it failed with a timeout after being stuck at 48% for 30minutes. Then all my other subscribers failed because of the same reason - the snapshot was obsolete.
In the end I tore down replication and re-created the publication and added all the subscribers again (this took all day too).
Now replication monitor is a lot more responsive.
Thanks for everyones help.
Friday, March 23, 2012
Rebuilding a huge clustered index
I have a client that has a 800GB table. The current clustered index on this table is of low selectivity and is causing index scans on queries. I wish to drop the current index and create a new one that is of more use. What i really want to know is, what is the fastest method of rebuilding such a huge index? What would be the storage requirements to process this?
That is indeed a very big table. Perhaps, you want to use index defrag (dbcc indexdefrag) instead of a full rebuild.
A good article on index defrag (sql2k) is here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
If you're on sql2k5, take a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx
|||Defragmenting the current index won't help, since you want a new index on different column(s).
If you are running SQL Server 2005 Enterprise Edition, you might try creating a new, non-clustered index with the ONLINE option and with MAXDOP set to something like 25% of your number of available CPU's (so it does not peg all the CPU's while building the index). This would be a lot less disruptive than dropping the clustered index and building a new one. This is assuming you need to stay online while you are doing this.
Without knowing the table schema, its hard to estimate the storage requirements for this.
|||The fastest way to rebuild a new index or create a new clustered index is to build/rebuild it in OFFLINE mode. If you rebuildin OFFLINE mode, we do minimally logging in the engine (we only log complete pages instead of individual inserts).The space requirements for ONLINE / OFFLINE are simular: you need space for the original index, you need space for the new index (simular size to the original index), and if you build the clustered index on new columns, you need space in tempdb as SQL Server will be doing a SORT before creating the new indexes. This totals on about 2.2 times the size of the original index (but could be larger, the 2.5 is just a rough guess)
So if perf is your only concern , use OFFLINE. The drawback is the table is offline, so other clients cannot access the table while the rebuild is in progress.
Also, the MAXDOP option will work for OFFLINE rebuild as well, so you can specify the number of CPU's you want to use during the rebuild.
Thanks,
Monday, March 12, 2012
Reboot, how often?
Hello,
We have noticed that we begin developing system stability issues with our
database client systems (everything is running on Windows 2K... SQL Server
is on a Win 2k Server system and the clients are running Win 2k Pro) after a
little over a month. A reboot of the server system seems to straighten out
this issue.
Has anyone encountered an article or information about a recommended reboot
time period for Windows 2000 Server systems running SQL Server?
Thank you for any help.Ideally, you should never have to reboot the server, except when the power
goes out or the system software is upgraded in a way that requires a reboot.
Are you sure that your application(s) or user(s) aren't causing memory leaks
or spiraled CPU usage? Any hints in the event log that might correlate to
these "stability issues"? What are these issues, specifically?
We have a windows 2000 server with SQL 7.0 in production, that is used quite
heavily throughout the day by many clients in both the US and Europe, and
the last time it was rebooted was when we changed data centers. I also run
several SQL Server 2000 systems that have never been rebooted for "stability
issues"...
"Michael" <somone@.somewhere.com> wrote in message
news:ud0Jwn9RDHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have noticed that we begin developing system stability issues with
our
> database client systems (everything is running on Windows 2K... SQL Server
> is on a Win 2k Server system and the clients are running Win 2k Pro) after
a
> little over a month. A reboot of the server system seems to straighten
out
> this issue.
> Has anyone encountered an article or information about a recommended
reboot
> time period for Windows 2000 Server systems running SQL Server?
> Thank you for any help.
>|||Having said that, Analysis Services can sometimes be a different issue
altogether. :-)
> We have a windows 2000 server with SQL 7.0 in production, that is used
quite
> heavily throughout the day by many clients in both the US and Europe, and
> the last time it was rebooted was when we changed data centers. I also
run
> several SQL Server 2000 systems that have never been rebooted for
"stability
> issues"...
We have noticed that we begin developing system stability issues with our
database client systems (everything is running on Windows 2K... SQL Server
is on a Win 2k Server system and the clients are running Win 2k Pro) after a
little over a month. A reboot of the server system seems to straighten out
this issue.
Has anyone encountered an article or information about a recommended reboot
time period for Windows 2000 Server systems running SQL Server?
Thank you for any help.Ideally, you should never have to reboot the server, except when the power
goes out or the system software is upgraded in a way that requires a reboot.
Are you sure that your application(s) or user(s) aren't causing memory leaks
or spiraled CPU usage? Any hints in the event log that might correlate to
these "stability issues"? What are these issues, specifically?
We have a windows 2000 server with SQL 7.0 in production, that is used quite
heavily throughout the day by many clients in both the US and Europe, and
the last time it was rebooted was when we changed data centers. I also run
several SQL Server 2000 systems that have never been rebooted for "stability
issues"...
"Michael" <somone@.somewhere.com> wrote in message
news:ud0Jwn9RDHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have noticed that we begin developing system stability issues with
our
> database client systems (everything is running on Windows 2K... SQL Server
> is on a Win 2k Server system and the clients are running Win 2k Pro) after
a
> little over a month. A reboot of the server system seems to straighten
out
> this issue.
> Has anyone encountered an article or information about a recommended
reboot
> time period for Windows 2000 Server systems running SQL Server?
> Thank you for any help.
>|||Having said that, Analysis Services can sometimes be a different issue
altogether. :-)
> We have a windows 2000 server with SQL 7.0 in production, that is used
quite
> heavily throughout the day by many clients in both the US and Europe, and
> the last time it was rebooted was when we changed data centers. I also
run
> several SQL Server 2000 systems that have never been rebooted for
"stability
> issues"...
Wednesday, March 7, 2012
Really strange behaviour concerning stored procedures
I have an application which comprises a VB6 client/server with a SQL
server back end. All the queries that the application uses are in
stored procedures which are initially created and occasionally
recreated by the application itself. The application does this by
building a CREATE PROCEDURE... SQL statement and then executing this
via the execute method of an ADO connection.
Any stored procs that involve 'Activities' are quite database intensive
as they use a view which is a multi-union view from lots of tables.
However, until now, once these stored procedures are compiled they run
pretty fast (typically 1 or 2 seconds).
Here's the problem (which has just started happening): when the
application creates (or recreates) a stored procedure involving
Activities, the query now takes about 10 seconds (instead of the
previous 1 or 2 seconds). However (and this is the really strange
part) when I get the procedure definition SQL from the stored proc and
execute it in Query Analyzer to recreate the SP, it creates a stored
proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
This is consistent (to a point) - each time I rebuild a stored proc
through the application the SP executes slowly and each time I create
it via Query Analyzer it executes quickly. In addition to this (just
to complicate things further!) after I've been testing it repeatedly
for a while it sometimes starts behaving ok - i.e. the stored proc runs
fast all the time regardless of whether it is created via the
application or via Query Analyzer.
I have tried this on our development SQL server and on a local MSDE
instance and I have tried it with different copies of the database -
the problem occurs in all tests so it doesn't seem to be db or server
related. I've noticed that the execution plan differs depending on how
the stored proc is created so I guess this is what's causing the big
time difference but the question is why should it matter how the stored
proc is created? The data is unchanged between tests and the stored
procedure text is the same - the only thing that changes is how the SP
is created (i.e. my application or Query Analyzer).
I have been tearing my hair out on this - can anyone please offer a
suggestion that might assist?
IanRead up on the SQL Server "procedure cache" and see if this would play a
role in what you are observing. Using SQL Profiler, you can trace
SP:CacheMiss, SP:CacheHit and other cache related events to determine what
is going on behind the scenes when your SP is being created or executed.
<ian__@.hotmail.com> wrote in message
news:1116345172.130396.298070@.g44g2000cwa.googlegroups.com...
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||Hi
Why do you need to re-create the stored procedures all the time? Usually
stored procedures are static code and you just change the values of the
parameters passed to them!
John
"ian__@.hotmail.com" wrote:
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||Are them being created with the same schema or owner in both cases (app and
QA)?
AMB
"ian__@.hotmail.com" wrote:
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||>> All the queries that the application uses are in stored procedures
which are initially created and occasionally recreated by the
application itself. The application does this by building a CREATE
PROCEDURE... SQL statement and then executing this via the execute
method of an ADO connection. <<
So you are such a bad SQL programmer that a random front end user
should be able to re-arrange the database. How did you expect to have
any data integrity?
If you had followed basic software engineering principles, the stored
procedures would be written, controlled and executed in the database
and not by the front end. This has nothing to do with SQL. This is
the foundations of all programming.
You need to start over, get a book on basic software engineering and
re-write what you have. As a rule of thumb, when you have "a
multi-union view from lots of tables", you usually have serious schema
design flaws.
Read about procedure casches, too. That is why dynamic things vary in
speed.|||On 17 May 2005 08:52:52 -0700, ian__@.hotmail.com wrote:
(snip)
>I have been tearing my hair out on this - can anyone please offer a
>suggestion that might assist?
Hi Ian,
First, let me state that I fully agree with the doubts expressed by John
Bell and Joe Celko regarding your design. I also agree with the possible
causes brought forward by JT and Alejandro Mesa.
But another possible explanation is this: check out the settings for the
options SET QUOTED_IDENTIFIER and SET ANSI_NULLS when creating the
procedure from QA or when creating it from ADO. These settings are saved
with the procedure when it's created (or rather: they are encoded into
the execution plan). A different value for one or both of these options
can result in a different execution plan.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Firstly, thanks to those who responded in a constructive and courteous
manner to my question - there's always a danger when posting that
small-minded individuals are going to respond with a load of unhelpful
and ill-informed comments. I really don't know how people can post
spiteful criticism based on assumption!
For the record, the application is of an extremely sophisticated nature
and is designed to enable the end-users to create their own, very
complex and powerful queries using a comparatively simple user
interface. These queries are created as stored procedures as they do
not change often and are executed frequently. The application ensures
data integrity but perhaps the notion of such an advanced design is
beyond people like CELKO?
Ian|||Thanks and congrats to Hugo! ANSI_NULLS were on in QA and off in the
ADO connection. I have amended the application code to set on before
rebuilding the stored procedure and the problem is fixed - I'm very
happy!
The reason this only started happening was due to a recent patch where
ANSI_NULLS were set OFF - this was not explicitly set before that
patch.
CELKO, see what can happen when you try to be helpful?
Ian|||On 18 May 2005 01:08:36 -0700, ian__@.hotmail.com wrote:
>For the record, the application is of an extremely sophisticated nature
>and is designed to enable the end-users to create their own, very
>complex and powerful queries using a comparatively simple user
>interface. These queries are created as stored procedures as they do
>not change often and are executed frequently. The application ensures
>data integrity but perhaps the notion of such an advanced design is
>beyond people like CELKO?
Hi Ian,
Actually, I think that Joe Celko has seen enough designs like this, AND
the results from it to make him very wary of this design.
Of course, Joe only sees the cases that have gone wrong (you don't pay
his rates to review a database that appears to be working fine), and
your situation might well be an exception, but still...
If you're allowing end users to write queries, then how do you gaurd
against the risk of injection of bad code? What do you do to prevent
someone including "DELETE FROM Customers WHERE 1 = 1" or "SHUTDOWN WITH
NOWAIT" or "EXEC sp_addrolemember 'System Administrators', 'Jeff'"?
Also, how do you gaurd against queries that run for hours, bringing the
database to it's knees or holding locks for so long that all concurrency
is lost?
If your end users are all developers and can be trusted not to do silly,
stupid, or even malevolent things, then why don't you simply grant them
the rights to add stored procedures and views, or to execute ad-hoc
queries against the database?
If your end users don't fall into this category, then you should not
give them a way to do development work they're not qualifeid for.
As I said - your situation might well be the exception. Not all
situations where designs like this have been implemented have
experienced the unwanteed side effects. But many do. I do hope that
you'll take Joe Celko's warning to heart - and that you seriously
consider other options.
(Since the queries don't change often, I'd set up a change request
system where the end users write stored procedures, sent them to a
skilled DBA or developer for review, and the latter executed the CREATE
(or ALTER) PROCEDURE script if the query is okay, or proposes
improvements and discusses them with the submitter of the query.)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> The application ensures data integrity but perhaps the notion of
such an advanced design is beyond people like CELKO? <<
LOL!! Of course you have always and will for the entire life of the
database, hire only *perfect* programmers. In the thousands and
thousands of lines of code they will write over time, nobody will
forget any business rules. Not one single rule! Amazing.
All the application code will use *exactly* the same algorithms. Never
mind that different programming languages use different truncation,
rounding, MOD() functions, string comparisons and so forth. The
perfect programmers will change the compilers or write their own
functions exactly the same way.
All third party packages will follow all of our business rules. How
they are going to do this when those rules are spread over thousands
and thousands of lines of application code, I don't know. Perhaps you
can tell me.
And when -- not if -- one of these integrity rules changes, the perfect
programmers will instantly propagate the changes in thousands and
thousands of lines of application code. And they will verify these
changes instantly.
And finally only perfect programmers will get to use QA or other tools
that go directly to the database without application code.
Advanced design? This is a return to a very primitive file systems
architecture. Talk to an old COBOL Programmer. The redundancy and
total lack of data integrity in those file systems are some of the
reasons we moved to DBMS and finally to RDBMS. You have re-discovered
1950's style ADP!
server back end. All the queries that the application uses are in
stored procedures which are initially created and occasionally
recreated by the application itself. The application does this by
building a CREATE PROCEDURE... SQL statement and then executing this
via the execute method of an ADO connection.
Any stored procs that involve 'Activities' are quite database intensive
as they use a view which is a multi-union view from lots of tables.
However, until now, once these stored procedures are compiled they run
pretty fast (typically 1 or 2 seconds).
Here's the problem (which has just started happening): when the
application creates (or recreates) a stored procedure involving
Activities, the query now takes about 10 seconds (instead of the
previous 1 or 2 seconds). However (and this is the really strange
part) when I get the procedure definition SQL from the stored proc and
execute it in Query Analyzer to recreate the SP, it creates a stored
proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
This is consistent (to a point) - each time I rebuild a stored proc
through the application the SP executes slowly and each time I create
it via Query Analyzer it executes quickly. In addition to this (just
to complicate things further!) after I've been testing it repeatedly
for a while it sometimes starts behaving ok - i.e. the stored proc runs
fast all the time regardless of whether it is created via the
application or via Query Analyzer.
I have tried this on our development SQL server and on a local MSDE
instance and I have tried it with different copies of the database -
the problem occurs in all tests so it doesn't seem to be db or server
related. I've noticed that the execution plan differs depending on how
the stored proc is created so I guess this is what's causing the big
time difference but the question is why should it matter how the stored
proc is created? The data is unchanged between tests and the stored
procedure text is the same - the only thing that changes is how the SP
is created (i.e. my application or Query Analyzer).
I have been tearing my hair out on this - can anyone please offer a
suggestion that might assist?
IanRead up on the SQL Server "procedure cache" and see if this would play a
role in what you are observing. Using SQL Profiler, you can trace
SP:CacheMiss, SP:CacheHit and other cache related events to determine what
is going on behind the scenes when your SP is being created or executed.
<ian__@.hotmail.com> wrote in message
news:1116345172.130396.298070@.g44g2000cwa.googlegroups.com...
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||Hi
Why do you need to re-create the stored procedures all the time? Usually
stored procedures are static code and you just change the values of the
parameters passed to them!
John
"ian__@.hotmail.com" wrote:
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||Are them being created with the same schema or owner in both cases (app and
QA)?
AMB
"ian__@.hotmail.com" wrote:
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||>> All the queries that the application uses are in stored procedures
which are initially created and occasionally recreated by the
application itself. The application does this by building a CREATE
PROCEDURE... SQL statement and then executing this via the execute
method of an ADO connection. <<
So you are such a bad SQL programmer that a random front end user
should be able to re-arrange the database. How did you expect to have
any data integrity?
If you had followed basic software engineering principles, the stored
procedures would be written, controlled and executed in the database
and not by the front end. This has nothing to do with SQL. This is
the foundations of all programming.
You need to start over, get a book on basic software engineering and
re-write what you have. As a rule of thumb, when you have "a
multi-union view from lots of tables", you usually have serious schema
design flaws.
Read about procedure casches, too. That is why dynamic things vary in
speed.|||On 17 May 2005 08:52:52 -0700, ian__@.hotmail.com wrote:
(snip)
>I have been tearing my hair out on this - can anyone please offer a
>suggestion that might assist?
Hi Ian,
First, let me state that I fully agree with the doubts expressed by John
Bell and Joe Celko regarding your design. I also agree with the possible
causes brought forward by JT and Alejandro Mesa.
But another possible explanation is this: check out the settings for the
options SET QUOTED_IDENTIFIER and SET ANSI_NULLS when creating the
procedure from QA or when creating it from ADO. These settings are saved
with the procedure when it's created (or rather: they are encoded into
the execution plan). A different value for one or both of these options
can result in a different execution plan.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Firstly, thanks to those who responded in a constructive and courteous
manner to my question - there's always a danger when posting that
small-minded individuals are going to respond with a load of unhelpful
and ill-informed comments. I really don't know how people can post
spiteful criticism based on assumption!
For the record, the application is of an extremely sophisticated nature
and is designed to enable the end-users to create their own, very
complex and powerful queries using a comparatively simple user
interface. These queries are created as stored procedures as they do
not change often and are executed frequently. The application ensures
data integrity but perhaps the notion of such an advanced design is
beyond people like CELKO?
Ian|||Thanks and congrats to Hugo! ANSI_NULLS were on in QA and off in the
ADO connection. I have amended the application code to set on before
rebuilding the stored procedure and the problem is fixed - I'm very
happy!
The reason this only started happening was due to a recent patch where
ANSI_NULLS were set OFF - this was not explicitly set before that
patch.
CELKO, see what can happen when you try to be helpful?
Ian|||On 18 May 2005 01:08:36 -0700, ian__@.hotmail.com wrote:
>For the record, the application is of an extremely sophisticated nature
>and is designed to enable the end-users to create their own, very
>complex and powerful queries using a comparatively simple user
>interface. These queries are created as stored procedures as they do
>not change often and are executed frequently. The application ensures
>data integrity but perhaps the notion of such an advanced design is
>beyond people like CELKO?
Hi Ian,
Actually, I think that Joe Celko has seen enough designs like this, AND
the results from it to make him very wary of this design.
Of course, Joe only sees the cases that have gone wrong (you don't pay
his rates to review a database that appears to be working fine), and
your situation might well be an exception, but still...
If you're allowing end users to write queries, then how do you gaurd
against the risk of injection of bad code? What do you do to prevent
someone including "DELETE FROM Customers WHERE 1 = 1" or "SHUTDOWN WITH
NOWAIT" or "EXEC sp_addrolemember 'System Administrators', 'Jeff'"?
Also, how do you gaurd against queries that run for hours, bringing the
database to it's knees or holding locks for so long that all concurrency
is lost?
If your end users are all developers and can be trusted not to do silly,
stupid, or even malevolent things, then why don't you simply grant them
the rights to add stored procedures and views, or to execute ad-hoc
queries against the database?
If your end users don't fall into this category, then you should not
give them a way to do development work they're not qualifeid for.
As I said - your situation might well be the exception. Not all
situations where designs like this have been implemented have
experienced the unwanteed side effects. But many do. I do hope that
you'll take Joe Celko's warning to heart - and that you seriously
consider other options.
(Since the queries don't change often, I'd set up a change request
system where the end users write stored procedures, sent them to a
skilled DBA or developer for review, and the latter executed the CREATE
(or ALTER) PROCEDURE script if the query is okay, or proposes
improvements and discusses them with the submitter of the query.)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> The application ensures data integrity but perhaps the notion of
such an advanced design is beyond people like CELKO? <<
LOL!! Of course you have always and will for the entire life of the
database, hire only *perfect* programmers. In the thousands and
thousands of lines of code they will write over time, nobody will
forget any business rules. Not one single rule! Amazing.
All the application code will use *exactly* the same algorithms. Never
mind that different programming languages use different truncation,
rounding, MOD() functions, string comparisons and so forth. The
perfect programmers will change the compilers or write their own
functions exactly the same way.
All third party packages will follow all of our business rules. How
they are going to do this when those rules are spread over thousands
and thousands of lines of application code, I don't know. Perhaps you
can tell me.
And when -- not if -- one of these integrity rules changes, the perfect
programmers will instantly propagate the changes in thousands and
thousands of lines of application code. And they will verify these
changes instantly.
And finally only perfect programmers will get to use QA or other tools
that go directly to the database without application code.
Advanced design? This is a return to a very primitive file systems
architecture. Talk to an old COBOL Programmer. The redundancy and
total lack of data integrity in those file systems are some of the
reasons we moved to DBMS and finally to RDBMS. You have re-discovered
1950's style ADP!
really isolated
Hi
i read microsoft.com
that "web servers do not require netbios or smb" (sic)
then MS recomends to disable smb by this way:
1) "Clear the Client Microsoft Networks box"
2) "Clear the File and Printer Sharing for Microsoft Networks box"
by this way, web server stays really isolated from the intranet
is this a good practice?
because, by this way
i cant move development files to webserver
and cant do replication
thanks
(this is the link
http://msdn.microsoft.com/library/d...
d89.asp)
atte,
Hernn Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . .
.The article dicusses;
"Disable all unnecessary protocols, including NetBIOS and SMB. Web servers
do not require NetBIOS or SMB on their Internet-facing network interface
cards (NICs). Disable these protocols to counter the threat of host
enumeration."
So, this is assuming that the web server has multiple nics installed, and
that one is internet facing. I think what's more important is to limit
the number of ports open on the box to 80 and/or 443.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||hmm...i see
ok thanks for the reply
atte,
Hernn Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . .
.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> escribi en el mensaje news:rHLgU
nZHEHA.660@.cpmsftngxa06.phx.gbl...
> The article dicusses;
> "Disable all unnecessary protocols, including NetBIOS and SMB. Web servers
> do not require NetBIOS or SMB on their Internet-facing network interface
> cards (NICs). Disable these protocols to counter the threat of host
> enumeration."
> So, this is assuming that the web server has multiple nics installed, and
> that one is internet facing. I think what's more important is to limit
> the number of ports open on the box to 80 and/or 443.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
i read microsoft.com
that "web servers do not require netbios or smb" (sic)
then MS recomends to disable smb by this way:
1) "Clear the Client Microsoft Networks box"
2) "Clear the File and Printer Sharing for Microsoft Networks box"
by this way, web server stays really isolated from the intranet
is this a good practice?
because, by this way
i cant move development files to webserver
and cant do replication
thanks
(this is the link
http://msdn.microsoft.com/library/d...
d89.asp)
atte,
Hernn Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . .
.The article dicusses;
"Disable all unnecessary protocols, including NetBIOS and SMB. Web servers
do not require NetBIOS or SMB on their Internet-facing network interface
cards (NICs). Disable these protocols to counter the threat of host
enumeration."
So, this is assuming that the web server has multiple nics installed, and
that one is internet facing. I think what's more important is to limit
the number of ports open on the box to 80 and/or 443.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||hmm...i see
ok thanks for the reply
atte,
Hernn Castelo
UTN Buenos Aires
. . . . . . . . . . . . . . . . . . . . . . . . .
.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> escribi en el mensaje news:rHLgU
nZHEHA.660@.cpmsftngxa06.phx.gbl...
> The article dicusses;
> "Disable all unnecessary protocols, including NetBIOS and SMB. Web servers
> do not require NetBIOS or SMB on their Internet-facing network interface
> cards (NICs). Disable these protocols to counter the threat of host
> enumeration."
> So, this is assuming that the web server has multiple nics installed, and
> that one is internet facing. I think what's more important is to limit
> the number of ports open on the box to 80 and/or 443.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Subscribe to:
Posts (Atom)