We are trying to troubleshoot some website performance issues and found some queries taking 2 to 3 seconds when the request comes from the web, and captured by the Profiler.
The same queries, when run in the Query Analyzer take 0 seconds.
What could be the reasons for this difference, I mean why it takes 2 - 3 seconds shown by the Profiler, when it's 0 second in Analyzer?
Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT Ａ.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?
View 2 Replies View RelatedHi All,What are the pros and cons between using Enterprise Manager or QueryAnalyzer for my queries.Currently I use Enterprise Manager because I prefer the interface andonly use Query Analayzer when queries time out in Enterprise Manager,but I'm sure there must be more to it.Regards,Ciarán
View 1 Replies View Relatedwhat are the new features provided by SQL management studio
View 4 Replies View RelatedNot sure why SQL Server displays different date formats for the same date in Enterprise Manager query window vs. SQL Query Analyzer.
When I see table rows through enterprise manager the date field looks like 3/18/2008 12:30:00 PM
Whereas when I run a query a query in Query Analyser/or through any other application the date is shown in a different format: 2008-03-18 12:30:00.000
Is there a setting in SQL Server that can remove the date format inconsistency?
Hi
I have a query which executes in 1 second and returning 14 rows whenever I execute it through Management Studio but when I execute it from a web page I am getting a timeout message and the stack trace is pointing towards the line which calls the stored procedure. The query is quite complex and there quite a few joins on tables and views including one to a lookup table, whenever I take out join onto the look up table the web page runs ok without timing out. I join the Lookup table (Parts) using a Left Outer Join similar to below. The syntax of the Join is fine but I cant find why it would cause the query to time out when executed from a webpage but is fine when I execute it from Management Studio
select *
from tables/view LEFT OUTER JOIN
Parts on Parts.PartNumber=tables/View.PartNumber
I've tried recompiling the Parts table but it didnt make any difference.
Any help would be much appreciated.
Cheers,
Frankie
I wrote a simple VB.NET utility program that uses a While...End While loop to go through all of the records in a table. Each time throgh that loop, an UPDATE command is assembled and assigned to the .CommandText property of an SqlCommand object. The command updates a record in another table by an ID field--based on the contents of the first table as well as the result of a lookup in another table. The update command is then executed each time within the loop via the .ExecuteNonQuery() method of the SqlCommand object. Again, it's important to point out that the update command updates only *one* record each time it is executed.
This program had been working fine until yesterday--when the query began timing out on the .ExecuteNonQuery() command. I have a counter within the loop and have been able to determine that the timeout occurs on the 3,359th time the query is called. The specific message is:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Re-running the program causes the same result at the same record.
I copied the query (whose ID updates the same 3,359th record) and executed it in SQL Server Management Studio; it timed out there, too. When I set the "Execution time-out" value to 0 (i.e., unlimited), the query ran for over an hour before I manually canceled it.
I can update all other tables in this database. I can also view (i.e., SELECT) the record that the update query is timing out on. I can also update any of the first 3,358 records in the SQLMS. However, the query continues to time out on the 3,359th record--and on any other record after that point.
The program was written on, and is running on, the SQL Server computer. The server has plenty of drive space. Both the database file and the log file for this database seem properly set to autogrow. I cycled the MSSQLSERVER service, but the query still times out at the same point. Rebooting the server did not resolve the problem, either. I don't see any errors in the Windows event logs.
What other methods can I use to try to resolve this problem?
I'm running into a a problem with Full Text searching. I've narrowed my code that produces the error down to this:
Code Block
SELECT Content
FROM Text
JOIN CONTAINSTABLE(Text, Content, 'lake') AS A
ON A.[KEY]=Text.ID;
and here is how I initiated the full text for the table:
Code Block
sp_fulltext_database 'enable'
sp_fulltext_catalog 'theCatalogSearch','create'
sp_fulltext_table 'Text','create','theCatalogSearch','PK_Text'
sp_fulltext_column 'Text','Content','add'
sp_fulltext_table 'Text','activate'
sp_fulltext_table 'Text','start_full'
sp_fulltext_table Text, 'Start_change_tracking'
sp_fulltext_table Text, 'Start_background_updateindex'
The first time I run it in the SQL query analyzer it sits there for about 30 seconds and then gives me "Timeout expired (error - 2147217871)." After the first query attempt I can run it as many times as I want and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try. I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.
I'm using Microsoft SQL Server 2005. The code I'm writing is pretty basic so maybe it's the way that the database is set up or the way I initiated the full text tables? Any help would be greatly appreciated. Thank you.
I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.
The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.
I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.
I have tested the trigger to do a simple task, and this works.
I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables
When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.
I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!
I'm running into a problem with Full-Text searching. I have a procedure which uses a full-text search.
When I run it in SQL query analyzer €“ it runs immediately.
I exec this procedure from my ASP page and it returns timeout error.
After the first query attempt I can run it (executing the ASP-page) as many times as I want with different search words and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try.
I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.
I'm using Microsoft SQL Server 2005.
Any help would be greatly appreciated.
Thank you.
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View RelatedI am trying to determine which of three stored procedure designs arefastest in the Query Analyzer:One query is a straight SELECT query with all desired rows and a dozen(tblName.RowName = @param or @param = Null) filters in the WHEREstatement.One query populates a #Temp table with the UniqueIDs from the resultsof the SELECT query in the above example, then joins that #Temp tableto get the desired rows.One query users EXEC sp_executesql @sql, @paramlist, @paramin which the @param has the dozen filters.What I'm trying to determine is which is the fastest.Each time I run the query in Query Analyzer it returns the samerecordset (duh!) but with much different Time Statistics.Are the Time Statisticts THE HOLY QRAIL as far as determining which isfastest, and what so I want to look at, the Vale or the Average? Inotice there are different numbers of bytse sen and bytes received foreach of the three queries.Any illumination on this is appreciated.lq
View 4 Replies View RelatedHi there
Running query analyzer against two different server.
the first only need 1-2 secs to return the query result,
the other return 7-8 secs for the query result.
plz advice what could cause this slow performance?
thx
Hi ,
I have query executed 1000 times a day by application with diffrent values in where ... but profiler shows different duration each time
duration might be 100 milisecond ,but might be 140000 miliseconds !!!
what is a reason ? should I belive profiler ?
SELECT cust_calls.call_date , cust_calls.work_order_sap , cust_calls.call_type_code , cust_calls.call_status , cust_calls.ind_max_expense , cust_calls.chng_date , cust_calls.user_id , cust_calls.call_seq_no , cust_calls.loc_equip_seq_no , cust_calls.initial FROM cust_calls WHERE (
cust_calls.loc_equip_seq_no = 3780 ) --changes with each query
ORDER BY cust_calls.call_date DESC
execution plan
|--Index Seek(OBJECT ([price].[dbo].[cust_calls].[cust_calls2]), SEEK ([cust_calls].[loc_equip_seq_no]=Convert([@1])) ORDERED FORWARD)
--space used--
name rows reserved data index_size unused
cust_calls 205019 201544 KB 74776 KB 38256 KB 88512 KB
-- table structure
CREATE TABLE [cust_calls] (
[call_seq_no] [numeric](7, 0) NOT NULL ,
[manufacturer_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[in_charge_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[work_order_sap] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[loc_equip_seq_no] [numeric](7, 0) NOT NULL ,
[call_type_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[syst_lang_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[maint_emp_code] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[expense_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ind_max_expense] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[call_date] [datetime] NULL ,
[call_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[call_description] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[chng_date] [datetime] NOT NULL ,
[user_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[Invoice_number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[invoice_date] [datetime] NULL ,
[invoice_amount] [numeric](9, 2) NULL ,
[invoice_note] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[initial] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[call_date_update] [datetime] NULL ,
[call_logon_id_update] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[gl_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[ind_follow_up] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
CONSTRAINT [PK__cust_calls__4EF38B7F] PRIMARY KEY CLUSTERED
(
[call_seq_no]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK__cust_calls__call___601E1781] FOREIGN KEY
(
[call_type_code]
) REFERENCES [cust_call_type] (
[call_type_code]
),
CONSTRAINT [FK__cust_calls__expen__267B85D1] FOREIGN KEY
(
[expense_code]
) REFERENCES [cust_expense] (
[expense_code]
),
CONSTRAINT [FK__cust_calls__in_ch__73BAFBDA] FOREIGN KEY
(
[in_charge_code]
) REFERENCES [cust_in_charge_warranty] (
[in_charge_code]
),
CONSTRAINT [FK__cust_calls__maint__40FA71E3] FOREIGN KEY
(
[maint_emp_code]
) REFERENCES [cust_maintenance_employees] (
[maint_emp_code]
),
CONSTRAINT [FK__cust_calls__manuf__41EE961C] FOREIGN KEY
(
[manufacturer_code]
) REFERENCES [cust_manufacturer] (
[manufacturer_code]
),
CONSTRAINT [FK__cust_calls__syst___1B29CEB6] FOREIGN KEY
(
[syst_lang_code]
) REFERENCES [cust_system_lang] (
[syst_lang_code]
),
CONSTRAINT [FK_cust_calls_cust_GL_Code_Type] FOREIGN KEY
(
[gl_code]
) REFERENCES [cust_GL_Code_Type] (
[gl_code]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Where is the information coming from that is displayed in queryprofiler.Is it the command that is retrieved before it is processed, or is itwhat is actually processed.
View 3 Replies View RelatedI am looking for a way to log all events that occur on the server. I have used quary profier and that gets me the info i need but is there a way to truncate the file, say only log the last 30 minutes of activity and having it rolling so that it is always the last 30 minutes. Or is there some other software that will do this. The database times out at random times and I am trying to fix the problem but dont want to keep houndreds of log files that query profiler will create by leaving a trace running 24/7. Any suggestions.
Thanks
Mark
Is Query Analyzer being droped in MS-SQL 2005? I'm using Microsoft SQL
Server Managemant Studio and I couldn't find the Store Procedure node
and Query Analyzer anymore. Help is appreciated.
Say you have a state column with the name of a state in it. And you need to add a branchId to the table. I know I should not have it in there, and the application should use proper lookup tables, but that's not the case.
Can SQL Server query analyzer loop through and update the branchid field based on a lookup in a lookup table?
How would that be done?
Hi All,
I think I post this in a wrong section but I cant find forum for sql server and i just have a quick question
basically, I installed sql server 2005 and I need to debug a store procedure.1. Does query analyzer come with sql server 2005?2. If not how can I debug store procedure in sql server 2005?Thanks in advance
Does anyone know if there is a limitation in Query Analyzer in terms of the number of characters in a query that it will parse?
I am having a problem with some large queries, and I am not sure if it is Query Analyzer, or the SQL Server query parsing engine that is causing the problem.
The query just involves a simple SELECT statement, albeit for a large number of columns.
Hi,
I'm using SQL Server 7.0. I connect to one database in Query Analyzer and I can use the "Perform Index Analysis" option under the "Query" menu option. when I connect to one of our other databases, the "Perform Index Analysis" option under the "Query" menu option is disabled (greyed out). Does anyone know what the problem could be and how might I be able to enable this option.
Thanks in advance,
Darrin
Hello
In query Analyzer of SQL2000 when i select
2 Select Queries and Run it shows in same Window as 2 Grids
I have seen people using seperate grid option to view each query results in a Tab query results Pane in MSSQL 7.
Eg Select * from table1 (in one grid)
select * from table2 (in another grid)
like a TAB so that i can move from one tab to another.
Is it possible to do that in query Analyzer?
Regards
I would like to use the Query Analyzer to create a script that will prompt the user(dialog box) for a response in SQL Server 7.0. I found a post someone mentioned DBMS can return messages and using Query Analyzer.
Thanks for your help in advance.
I'm trying to do a couple of things with some different tables.
With the first table, I have first name and last name in two separate columns.
I'm trying to create a new ID column that would do the following.
Take the first 3 letters of the last name (and remove any non characters such as ',&) and the first 3 letters of the first name, and then combine them.
I know how to use substring to take the first three, and I know how to combine them, but I'm having problems removing the non alpha characters.
For example, if the columns looked as follows:
First Last
John O'Connor
I'd want the result to look like OCOJOH, however, I'm getting O'CJOH.
The second issue is how would I do something similar if the First and Last Name were in the same column and string.
For example, if the column looks as follows:
Name
John O'Connor Publishing
I'd like to pull the first 3 from the first string before the space and then the next 3 so it would be as follows:
JOHOCO
Finally, I want to compare these new IDs, and add numbers to them.
So if I had the following
First Last
John O'Connor
Johnny O'Connell
The first would be OCOJOH01
The second would be OCOJOH02
If there are not any duplicates with the new ID, I'd like to call it OCOJOH01 and then move on.
Any help/ideas would be greatly appreciated.
Thanks
Hi;
I would like to change the format of dates that is in colum using query analyzer. The name of the colum is StartD and this how they look: 3292006 I would like to insert the / / . Can someone show me how to accomplish it.
Thank you
I have a database with couple of store procedure and views.
Is there any way that i can find out all sp or views which contains word like *svc*. I am talking about the containt of the sp and views not name.
We are replacing one module so i have to change the all sp and views which use table or view start with svc.
Need help.
Thanks
in the market place that will run a query on multiple DBs acrossmultiple servers? Without having to manually connect to each server?If so, can anyone provide a link for it.Thanks
View 3 Replies View RelatedI am new to SQL and need help running a performance check on our companies dtabase. I use SQL Enterprise Manager 2000. Could someone please help me accomplish this?
Thanks,
Carl
hi everybody!
this is my first post, so I'd like to say hallo to everyone!
I "work" on SQL Server 2000, and I would like to ask you a question.
Is it correct that i cannot set a database user as database owner (using command sp_changedbowner 'user_name')?
QA returns this error message:
"Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database."
so I have to use sp_revokedbaccess and then I can change the db owner.
why can't a db user be the db owner?
thanks for the answers!!
Hi,
Was wondering if anyone can tell me how to get the Query Analyzer Tool installed to WM6 Professional Emulator inside of VS05?
Thanks,
John.