The PASS summit that was held in Denver, CO last week was great! Stay tuned for some pictures that we’ll have available in a few days - many should make you laugh. Our experts are now back from their hiatus attending the summit and back to answering your questions - sorry for your patience throught the short layoff - KEEP SUBMITTING QUESTIONS!
THANKS!
Q: When I do this query:
SELECT cast(tbl.NSID as bigint) as NSID, tbl.Keil FROM [SM1].[DB1].dbo.[BlobsM1] as tbl WHERE tbl.NSID IN ( SELECT [ValuesM1/PrimaryKey]
FROM ( SELECT [SM2_DB1_ValuesM2].nsid as [ValuesM2/PrimaryKey], [SM1_DB1_ValuesM1].nsid as [ValuesM1/PrimaryKey], [SM2_DB1_ValuesM2].nsid as __SID__ , mp.*
FROM [SM2].[DB1].dbo.[ValuesM2] [SM2_DB1_ValuesM2] inner join [SCENTRAL].[DB1].dbo.[millPathes] mp on mp.[A7] = [SM2_DB1_ValuesM2].nsid
INNER JOIN [SM1].[DB1].dbo.[ValuesM1] [SM1_DB1_ValuesM1] on mp.A31 = [SM1_DB1_ValuesM1].nsid WHERE ( [SM2_DB1_ValuesM2].Solldicke_H0>330 ) And (isnumeric([SM2_DB1_ValuesM2].ID_NR) = 1 and [SM2_DB1_ValuesM2].nsid is not null) and ([SM1_DB1_ValuesM1].DateTime<>convert(DateTime,’2007-05-03 00:00:00′,110)) )MQ )
I got a result of 35 rows, where the column Keil is an image of 5kB size per row. The duration of this query is 36 minutes!!!!! When i do the same query with a datetime column instead of the image column, the execution time of the query is less than 1 second. The SQL Servers SM1, SM2 and SCENTRAL are all MSSQL Server. The network between the Servers is 100MBit and is tested OK. My Question is how to improve the performance of such distributed queries?
Kevin Kline says: You need to utilize the standard steps needed troubleshoot any query to determine what problem is causing the slowness.
First, check the query plan to find out which index is being utilized in the datetime query, but not in the image query. Then determine if there’s a way for you to use that same index in the second version of the query.
Second, check STATISTICS IO of the two queries to see if there are significant differences in overall IO. Since one version of the query uses images and the other doesn’t, I’m sure there’s a huge difference in IO between the two.
Finally, an extra tuning tip, see if you can rewrite the query substitutine the IN clause with an EXISTS clause. They usually perform much better. You might even be able to rewrite it as a JOIN, which is usually even better.
Technorati Tags:
SQL Server, distributed queries, SQL troubleshooting, SQL development
Q: How to use ” join” in SQL Server????
Bryan Oliver: Hi I would like to suggest a great reference for explaining JOINs at the blog site of Colin Angus McKay.
Technorati Tags:
SQL Server, JOIN, SQL, programming
Q: How to insert a New Column on a Table on a specific Index using SQL (script)?
Bryan Oliver: To insert a new column in a specific location other than at the end of the table use either SQL Server Management Studio or enterprise manager. The queries are quite complex as the table has to be dropped and recreated within SQL Server.
An alter table will only allow you to add a new column at the end of the table but will not require the complexity that is encountered through adding a column in a specific location.
If you are interested in the stored procs that MS calls than turn SQL Profiler on and watch the calls when you add the column.
Technorati Tags:
SQL Server management studio, SQL Server profiler, enterprise manager, alter table
Q: I have SQL Server 2005. I have to convert all the varchar Datatypes in all tables to nvarchar. (In short I have to convert the non unicode tables to unicode tables) Please let me know the code of how to do it.
Patrick O’Keeffe says: Do the tables have data in them? If not a simple script out of all the tables followed by a search and replace will do the trick.
If the tables have data you will have to write a script to:
1. drop any constraints
2. create a new table with the new nvarchar column
3. add the constraints you just dropped to the new table
4. copy the data from the old table to the new table
5. drop the old table
6. rename the new table to the old table name
7. add any indexes as appropriate
SQL Server Management Studio can generate a table change script from the table designer to study as an example.
Technorati Tags:
SQL Server 2005, unicode, non-unicode, datatypes, datatype conversions
Q: How do I change a SQL port number?
Iain Kick says: OK, I pulled this straight out of Microsoft books online, so let me make sure I’m giving full disclosure that - I’m promoting their expertise on this one 
Here is what BOL says about changing port numbers:
How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
If enabled, the default instance of the Microsoft SQL Server Database Engine listens on TCP port 1433. Named instances of the SQL Server Database Engine and SQL Server Mobile are configured for dynamic ports, which means they select an available port when the SQL Server service is started. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.
To assign a TCP/IP port number to the SQL Server Database Engine
In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration, expand Protocols for , and then double-click TCP/IP.
In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear, in the format IP1, IP2, up to IPAll. One of these are for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you wish to configure.
If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
In the IPn Properties area box, in the TCP Port box, type the port number you wish this IP address to listen on, and then click OK.
In the console pane, click SQL Server 2005 Services.
In the details pane, right-click SQL Server () and then click restart, to stop and restart SQL Server.
After you have configured SQL Server to listen on a specific port there are three ways to connect to a specific port with a client application:
Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.
Create an alias on the client, specifying the port number.
Program the client to connect using a custom connection string.
Technorati Tags:
SQL Server, Microsoft Books Online, ports, configuration manager, database engine listeners
Many of the experts that are answering your questions will be attending and available to meet you September 17 - 21 at this years PASS conference in Denver, CO. Come to the Quest Software booth and meet Kevin Kline (who is also the PASS president), Bryan Oliver (SQL Server domain expert), David Gugick (SQL Server MVP and director of product management) and Ari Weil (SQL Server domain expert and development manager) - all of which contribute to this site to answer your questions and to help share their knowledge of the SQL Server environment.
Q: If you run BACKUP LOG [ASCEND-HI] WITH TRUNCATE_ONLY; DBCC ShrinkDatabase ([ASCEND-HI], TRUNCATEONLY); do you loose the ablity to do a Point in Time recovery?
James Delve says: When you perform a backup Log with truncate only you are essentially purging the log and forcing the contents into the DB. This allows you the ability to shrink the log and reclaim the now free space back.
However, because there is physically no ‘log backup’ created, so therefore you can’t actually restore it, either in it’s entirity, or to a point in time.
Technorati Tags:
SQL Server, Point in time recovery, backup log, shrinkdatabase
Q: How can I find the description of an error in reporting services?
Kevin Kline says: This is a little unclear since I don’t have your error number and error message for me to give you more help.
In the meanwhile, here’s a general troubleshooting process to follow:
1. Write down the error number. If you don’t have the error number, write down as much of the error as possible.
2. Press Ctrl-PrtSc to save a screenshot of the error msg to the clipboard. Save it in Paint for later reference.
3. Go to http://support.microsoft.com
4. Click the hyperlink “Switch to Advanced Search” in the upper right corner of the frame. Then:
A. In the “Search Product” box, enter SQL Server, SQL Server 2000, or SQL Server 2005.
B. In the “For” box, enter the error number and/or the error message.
C. You should be able to leave the rest of the choices as-is.
D. Make sure that Include boxes are checked: How-to Articles, Downloads, Troubleshooting, Guided Help, MSDN content, TechNet Content.
5. Click Search.
Now, look at the links that are returned and choose the one(s) that work best for your problem.
Hope this helps
Technorati Tags:
SQL Server, reporting services, MSDN, TechNet
Q: How could i change the @SPID while using a procedure?
Patrick O’Keeffe says: You can’t - T-SQL code being executed always runs in the context of the spid that initated the execution.