I have a user who is complaining of delays when she queries a database over a remote desktop connection. As I have no experience with this, I was wandering if there were any experts out there on how to improve remote performance in SQL Server 05. Any suggestions are appreciated.
View 3 Replies- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
Given the following scenario, what kind of performance should be expected in transferring about half a million rows? We are seeing about a 9 minute execution time. Is this reasonable for about 460,000 records moving from source to target, with 3 inner joins from the source?
Source: Server A.OLTPDB
Target: ServerA.DataMartDB
Server A is running SQL Server 2000. SSIS is running on a different machine, Server B.
The reason for this is that we are distributing the SSIS package for use with a BI product built on SSAS 2005 and the requirements are such that the client could very well have the source OLTP database on a different physical machine than the data mart.
My understanding is therefore that:
1. SSIS will do all of the heavy lifting on Server B.
2. Even though OLTPDB and DataMartDB are on the same server, it is expensive for Server B to pull the records from Server A and then send them back to Server B, but with SSIS being on a different machine, this is inevitable.
3. In the OLE DB Source Adapter, specifying table or view has the effect of an OPEN QUERY command, whereas a SQL command with straight SQL will be executed on Server B and the former would be somewhat more performant.
Can you guys validate/dispel these assumptions?
TIA,
Rick
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedHello, I have a sql server installed on a server in my server room.
The problem I have is that when I try to connect to the server, it takes a loooooooooong time to open the connection. Even when connecting through the Sql Server Management Studio it takes about 30 seconds just to open the connection.
Can anybody help me with some pointers on how to troubleshoot this issue? It only happens with this specific server
It might be a configuration issue, but where can I find information on how to configure it correctly?
I have a table that has triggers on insert update and delete. The triggers perform modifications on another table on a remote databased(linked server).
All triggers make a join between the tables inserted, deleted and the remote table.
When running a profile on the remote database it seems that the following query is done on the remote table by the triggers.
"Select * from Database.dbo.Table"
The triggers are the only entities accessing the remote table from the original server so the query must come from them. My only conclusion is that MSSQL server is doing this query for some kind of "optimization".
Has anyone seen this before?
How can I work arond this? The remote table is BIG and this query happening every few seconds in a problem for me.
Thanks
Robert
I just want to find out what the difference between a remote login and a remote query is. I'd have thought that a remote query inherently requires a remote login.
View 6 Replies View RelatedI set up the collector, and specify the Run As as my AD account in the Collector Set - Properties - General screen. My AD account is the local admin of the remote server.
However, the collector does not seem to work. Although the collecting set is shown as running, the The blg file stays at 64K. If I open it, there is nothing inside (no counter at the bottom). What did I miss?
I can query another server from the query analyzer -
SELECT COUNT(*) FROM Dbmsql09.address.dbo.str_name
But if I try to do this from within a stored procedure I get the error msg "set ansi_nulls and ansi_warnings then retry"
My question is this: how do I set the 2 options? Ive tried set ansi_nulls on within the stored procedure and its ignored.
Ive set it, then recreated the stored procedure - same error.
Any suggestions?
Thanks a lot.
Scott
Hi, all -
I know you can execute stored procedures on remote servers in SQL Server 6.5, but can you run regular queries as well using the 4 part name qualification?
Example: select * from server1.database1.dbo.table1
If not, is there another way I can manipulate data on remoter servers?
I am getting the following error:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [DataDirect][ODBC Sybase Wire Protocol driver]Insufficient information to connect to the data source.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Any idea?
Thanks, YB
Hi..
Is there any specific place where I can find which SQL Query is more efficient?.
Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is faster?
I have two tables:
1.FLEET (The number of rows is not so much)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
Fleet_Name
Fleet_Description
2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number of row in fleet table)
Attributes :
Company_Id (PK)
Fleet_Id (PK)
User_Id (PK)
Privilege_Id(PK)
Comment
Category
I want to select Fleet_Id and Fleet_Name from fleet table
Where the current user has privilege_id=1
I have two possible select statement :
1.Option 1
SELECT Fleet_Name, Fleet_Id
FROM FLEET
WHERE (Company_Id = 2) AND (Fleet_Id IN
(SELECT fleet_id
FROM user_privilege
WHERE user_id = 11 AND company_id = 2 AND privilege_id = 1))
ORDER BY Fleet_Name
2.Option 2
SELECT F.Fleet_Name, F.Fleet_Id
FROM USER_PRIVILEGE U INNER JOIN
FLEET F ON U.Fleet_Id = F.Fleet_Id
WHERE (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id = 11)
ORDER BY F.Fleet_Name
Actually which one is faster. Is SQL Statement with INNER JOIN (Option 2) can be executed faster than the one with double Select Statement(Option 1)?
Any suggestion is welcomed.
Thank you very much.
Hi:
I have the following query, can somebody help me?
SELECT s.Id, s.NameFROM Switch s INNER JOIN SwitchTelephoneRange r ON s.Id = r.IdWHERE '1526858' BETWEEN FromTelephone AND ToTelephone
Where the '1526858' is a phone number.
My problem is, I want to run the above query for each record in :
select Telephone from PhoneDirectory
So, each telephone number in the second phone, would macth the ' ' in the first query.
How can I do so? Do I need a loop? a cursor? Can you help please?
Thanks
Hi,
I want to know query execution time as output. I want execution time only. this is for tuning purpose... Time displayed in the status bar is not helpful for me.
thanks.
Hi there,
I'm having a big performance problems with sql query. What i have done is changes physical layout (rearanged the columns) in one of the tables in the database. I used bcp to get the data out and then in. There are about quarter million rows in the this table. I have created the same indexes but know the same query takes a long time to execute. I have noticed that the showplan is somehow different than it used to be. This query uses the table i have changed and another one that i haven't touched. I have updated the stats to no help.
Here are the show plans.
this one is slow
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
SW_PERSON
Nested iteration
Index : swiPERSON10
FROM TABLE
SW_CASE
Nested iteration
Table Scan
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan
this one used to be fast
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
SW_CASE
Nested iteration
Table Scan
FROM TABLE
SW_PERSON
Nested iteration
Index : PK_SW_PERSON_1__27
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan
I think the problem is with the fact that the fist one doesn't use the PK
key which is the one the links both tables. My question is how to force the query to use this index.
PS.
One thing i haven't done is to recreate indexes on the other table. But i don't think that would have made a differnece.
Thanks
I have the following functions in my database
Code:
CREATE FUNCTION dbo.fnProductsRetrieveBySupplierID
(
@SupplierID int
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Products
WHERE SupplierID = @SupplierID
)
CREATE FUNCTION dbo.fnSuppliersRetrieveBySupplierID
(
@SupplierID int
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Suppliers
WHERE SupplierID = @SupplierID
)
I have been testing the performance of the following SQL statements:
Code:
1.
SELECT *
FROM Products INNER JOIN
Suppliers ON (Products.SupplierID = Suppliers.SupplierID)
WHERE Products.SupplierID = 3
2.
SELECT *
FROM dbo.fnProductsRetrieveBySupplierID (3),
dbo.fnSuppliersRetrieveBySupplierID (3)
I have built a loop to execute each statement multiple times and then compare the execution times. Although both queries produce the same result, the 2nd one (which uses the functions) is about twice as slow, does anyone know why?
thanks
Hi All,
I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.
All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.
query looks some thing like this.
insert into table d (col1,col2,col3,.............. )
values (a.col1,b.col2,c.col3 .....................)
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @minid and @currid
The @minid starts from 1 and @currid starts from 50000 ,I am running this in a loop, in next iteration @minid will become 50001 and currid 100,000 and so on.
Any idea why it is taking that much time?.
Thanks in advance,
bsr.
I have two tables.
Employee
EmployeeCode int Primary Key
Employee_Stock
EmployeeCode int
StockCode varchar(10)
Primay key on (Employeecode, StockCode)..
There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode
2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.
Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?
Thanks
Ramesh
I wrote a query and I use cursor in the query. When I run the query on dev box it takes 3 mins.
I moved the query to EPM database box and it takes forever to run.
Usually EPM database query performance is much better. How do I start debugging the poor performance?
How can I check if the query is creating any table locks?
Purpose of query: I get all the Companies (20000) and loop thru each company in the cursor and do calculations.
Thanks...
Hi all
I want to check the query performance
How I should starT
I mean what is the plan to be followed
Thanks In Advance
Malathi Rao
I have a query like below and it takes a aouple of seconds to run
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider
However, if i add a served field( stamped with 1) it takes forever to run.. All of join columns have indexes.. cluster and non-clustered.. and i don;t see any index fregmentaitons...
select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider, served from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider
group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider, served
Hi!
Sorry for bad english, I'm from Brazil.
In microsoft.public.sqlserver.ce haven't found a way to improve performance
of this query. Thanks for any help or reply!
This used to take almost 6 min !!! With index on E.Produto now takes about 30 sec...
1,909 row table
Ipaq 1950 - Samsung 300 Mhz - 32 MB RAM - Windows Mobile 5.0 - SQL CE
2.0
PK (all multiple columns) - tables:
Lotes - pk(Empresa, Lote, Contagem, Produto)
Contagem - pk(Empresa, Lote, Contagem, Produto)
Produtos - pk(Codigo) // this field also is FK <=> Produto in all other
tables
Estoque - pk(Empresa, Ordem, Produto)
Part of my VB.NET code with SQL:
sql_grd_inv = "SELECT L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Lotes L " _
& "LEFT JOIN Contagem C ON (L.Empresa = C.Empresa) " _
& "AND (L.Lote = C.Lote) AND (L.Contagem = C.Contagem) " _
& "AND (L.Produto = C.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (L.Empresa = E.Empresa) " _
& "AND (L.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON L.Produto = P.Codigo " _
& "GROUP BY L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde " _
& "HAVING (L.Empresa='" & IncEmpresa & "') " _
& "AND (L.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (L.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') " _
& "UNION " _
& "SELECT " _
& "C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Contagem C " _
& "LEFT JOIN Lotes L ON (C.Empresa = L.Empresa) " _
& "AND (C.Lote = L.Lote) AND (C.Contagem = L.Contagem) " _
& "AND (C.Produto = L.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (C.Empresa = E.Empresa) " _
& "AND (C.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON C.Produto = P.Codigo " _
& "GROUP BY C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde" _
& ", L.Empresa, L.Lote, L.Contagem, L.Produto " _
& "HAVING (L.Empresa Is Null) AND (L.Lote Is Null) " _
& "AND (L.Contagem Is Null) AND (L.Produto Is Null) " _
& "AND (C.Empresa='" & IncEmpresa & "') " _
& "AND (C.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (C.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') "
Hello SQL Gurus,
From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?
select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc
Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...
Sincerely,
-Lawrence
Hi,
I am wondering if someone can clear my mind about a performance doubt.
What performs best using inner or left joins... I filter my data in the ON clauses or in the WHERE clauses...
Example 1:
FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID = B.ID AND A.ID IN (@IDS)
Example 2:
FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID = B.ID
WHERE A.ID IN (@IDS)
Consider that table A and B can contain large sets of data.
Best Regards,
Luis Simões
I usually am all over answering these kinds of questions, but while I continue to work on this issue, maybe someone here can lend me a hand. A vendor application we run, stores metadata about backup blobs stored on a NAS device. The app basically backs up select folders on 1400 remote computers in the back office of our stores, and stores this on a NAS, while maintaining metadata about the BLOBs in SQL Server so that they can push recovery of the data back to the original store it came from. The database is roughly 80GB in size and has a single file group and is on its own dedicated LUN. It uses TempDB heavily, and this is not something that I can change, but TempDB is on a different disk array.
Today I spent hours on a conference call with them looking at a specific stored procedure that is used to clean up the records in the database after a BLOB file is deleted. A single BLOB file can have millions of related records in the database. There is a LEFT JOIN in the code that is against a table with 150 million + rows of data in it. The table size is fairly small, only 5 GB of data, but the LEFT JOIN spools 2.4GB of data to a Hash Match. It seems to me like the left join can't be removed, but I don't get how all of this works, because I didn't write the application. It is an INDEX SCAN. I can't seem to eliminate it. Is there anything I can do to help this thing out?
I am attempting to get a better understanding of why my SQL 2005 setup when running a simple select statement on a large table is displaying very low IO in performance monitor. If i run a single Select * From testtable i see 4mbsec transfer and Disk readssec is around 8-9. This particular table is sitting on a single U320 10k drive so i expecting to see far more substantial IO. Does anyone have any information on how IO is consumed using different SQL operations so i can obtain a better understanding?
View 2 Replies View RelatedSo I am experimenting with upgrading a Windows Mobile application from .NETCF 2.0 to .NETCF3.5, along with moving my SQL 2005 Compact to SQL Compact 3.5. I have a database that I upgraded using the recommended methods (creating a datasource in VS2008, opening the SQL 2005 Compact .sdf file and allowing the tool to upgrade to SQL Compact 3.5). On the device (Dell Axim x51), with the .SDF files on an SD Card, the query, when executed against the SQL 2005 Compact database file, takes 1.5 seconds, but takes 1min41sec to execute on the SQL Compact 3.5 database.
This is a fairly simple query, with an inner join (using about 4 inner join constraints), a where clause (over about 3 things), and an order by clause. The execution plan for the SQL Compact 3.5 query shows index seeks (one consuming 2% and the other consuming 0%, with the inner join using 98%). The database files are on the order of 90MB.
Can anyone offer any suggestion why the SQL Compact 3.5 query performance would be so much worse than the SQL 2005 Compact performance?
Thanks,
Matthew Belk
The below query seems to be very slow :
select distinct a.* from test a inner join test1 b on b.col1 = a.col1
inner join test2 c on c.col2 = a.col2 where exists (select NULL from
test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have
index. Is there any alternative available for the above which can
increase the performance ?
Please advice,
Thanks,
Smitha
I have an sql 7.0 server. I need to run a query on a remote oracle server then take that output and inner join it with another table on the remote oracle server... then I need to put it a table in a database on my sql server... Anyone know how to do it? Help would be greatly appreciated...
View 2 Replies View RelatedA SQL server 7 database I have been developing has recently been moved outside a firewall (for various reasons). I have found that I cannot access query analyser or enterprise manager via an IP address. Is their any way around this?
View 2 Replies View RelatedWhat is a remote connection and query ? And what is a local connection and query? If i'm on management studio on my pc connected to a SQL Server 2012 that is on a server, am I remote ? I didn't had a timeout in SSMS for the following query. Considering the remote query timeout setting is 600
USE Database_name
go
BEGIN
WAITFOR DELAY '00:11';
select * from [dbo].[Table_Name]
END;
GO
Hi,Im running into a strange problem here!I have created a linked server on one of my SQL Servers (local server).Lets call the linked server as LS1.I have an sproc which resides on the remote server(LS1) and uses a FORXML query.On running this sproc from my local server using linked server LS1, Iget a strange serialized data as output instead of the usual XMLoutput.I run the proc asexec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'the proc accepts 2 parameters as dates.The output generated is smth like:0x44046E0061006D0065004404440061.......(long string of hex codes)Looks like the ouput is getting serialized. How can I avoidserialization OR How to de-serialize this data?Please HelpThanks,Geo
View 5 Replies View RelatedHello,
Here I have a small doubt about validating UserName and Password.I validate username and password with following quey (forget about case-sensitiveness of password) :select password from table where username='Uname' and password='pwd';Now in second scenario, I use following :select password from table where username='Uname'and validate password in .NET code.1) If user having 'Uname' does not exists in database then which query is faster (first or second)?2.1) If user exists and password is not matching then which is faster?2.2) 2.1 + If there is clustered index on username column, is first query optimized?
Thanks