SQL Server
Inactive processes can quickly eat up connections to your
SQL Server databases, dramatically limiting the number of connections available
for other work.
Gettting information about locks can be done using sp_lock system procedure.
Kill a process from from Enterprise Manager:
1. Expand a server group, and then expand a server.
2. Expand Management, and then expand Current Activity.
3. Click Process Info. The current server activity is displayed in
the details pane.
4. In the details pane, right-click a Process ID, and then
click Kill Process.
5. Confirm that the process has terminated.
Also, you can dynamically terminate a particular process by
executing a kill statement, such as
kill n
Current process
Use @@SPID for getting the server process identifier (ID) of
the current user process.
Sample:
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS
'User Name'
Below
function takes as parameter a text, split the text in tokens, and returns a
table containing all this tokens.
CREATE FUNCTION ParseText2File(@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while
len(@p_text) > 0 and charindex(@p_delimiter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_delimiter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_delimiter,@p_text))
end
insert @tempResults select @p_text
insert @results
select result
from @tempResults
return
END
Let’s see a practical case when this function can be used. You have a list
of products. Each product belongs to a specific category. You need to return
all products belonging to a list of categories.
An approach could be to create a stored procedure GetProducts(@categories
varchar(4000)) which has a parameter containing all categories ids separated by
comma like this: '1,3,6,11'. Using the above function,
the solution of our problem is simple:
CREATE PROCEDURE
GetProducts(@categories varchar(4000))
As
SELECT p.productId, p.productName
FROM products p
WHERE p.categoryId IN (SELECT id
FROM dbo.ParseText2Table(@categories))
A different approach can be found here:
http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
P.S. Of course, you can take in consideration sp_executesql function.
The Query Analyzer can be used to recommend indexes for
specific tables. By entering a query into the Query Analyzer, and running the
"Index Tuning Wizard" option, the query will be reviewed, and if
appropriate, one or more indexes will be recommended by the Index Wizard. If an
index is recommended, Query Analyzer can automatically create the index for
you, if you like. This tool will not point out indexes that are not used, nor will
it affect any existing indexes.
One way I take advantage
of this tool when tuning a query is to run this option as a first step, before
I really begin any analysis and work on the query. This way, if there are any
obvious indexes needed, they will be found quickly, saving me a little time.
Once this step is out of the way, then I run an execution plan of the query,
and then look for other ways to tune the query.
Did you tried to execute TRUNCATE statement against a table on a
remote server? TRUNCATE TABLE server01.database01.dbo.Tablename01
Executing the above statement, you’ll get the error message:
Server: Msg 117, Level 15, State 1, Line 2
The object name 'server01.database01.dbo.' contains more
than the maximum number of prefixes. The maximum is 2.
TRUNCATE TABLE isn't a command that you can run directly by using the
four-part name associated with a linked server. However, you can issue the
TRUNCATE TABLE command against the linked server by using the sp_executesql
stored procedure. Sp_executesql is designed primarily to help you parameterize
a SQL query so that SQL Server can reuse the plan more easily. However,
sp_executesql is also valuable when you're running commands against linked
servers.
EXECUTE sql3.master.dbo.sp_executesql
N'TRUNCATE table tempdb..NewAuthors'
The above statement hows an example of how to use sp_executesql to execute
SQL commands on a remote server even if the native commands don't directly
support linked servers. Although this example demonstrates the execution of
TRUNCATE TABLE, you can use this stored procedure to execute almost any type of
SQL command.