Hi guys,
Do you have any stored procs or utility to track down the lead blocker as well as the blocked processes on SQL Server 2000? Similar to a tree structure with the lead blocker on top followed by the processes being blocked by the lead blocker.
Thank you very much.
Hi Guru,After spening quite sometimes to watch my box, I've seen PAGEIOLATCH isa lead blocker in my SQL Server 2000 server. Below is the detailed:SPID lastwaittype waitresource blocked status cmd57 LCK_M_S KEY: 7:963690681:8 65 sleeping execute65 PAGEIOLATCH_SH 7:1:217904 0 sleeping selectI thought, latching should be very short-term synchronization. Fromsystemprocess table, I saw the latch waited in a minute sleepingwithout doing any work.My database is about 23GB and more than 5000 tables. The RAID subsystemis RAID1 with 1 disk mapped to C and D logically. Data files and tempdbfiles are located in one location. Tranlog file and log backup filesare located in the same location with different disk spindle.Currently, we are experiencing very slowness and IO bound. I'm ready torebuild the server by putting the RAID10 and 1 and distributingmultiple data files to different RAID10 and tempdb and log files toRAID1.Other than this, how to minimize the IO latch contention?Thanks so much,Silaphet,
View 1 Replies View RelatedApp developers are complaining that a blocking report I sent them today does not have enough info to troubleshoot and fix the root cause of the blocking.(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
i want to create a new table from one table like this
TIME_ID SALES LAG1 LEAD1
--------- ---------- ---------- ----------
10-OCT-00 238,479 23,183
11-OCT-00 23,183 238,479 24,616
12-OCT-00 24,616 23,183 76,516
13-OCT-00 76,516 24,616 29,795
14-OCT-00 29,795 76,516
i think i use some functions in my stored procedure can anyone help me about this stuation Thanks!!
Starting with a SQL Server 2000 database, my company built a new server with SQL Server 2005 and restored the SS2000 database from backups. We're now seeing some extreme issues where queries that took 30 seconds now take 3 minutes. Would this make you suspect index issues? Any recommendations on diagnosing and fixing this would be appreciated.
View 4 Replies View Related-- Business Rule, first name, middle name and last name can all be null-- ddlcreate table #cat (catID char(8) primary key, first_name varchar(15)null, middle_name varchar(2) null, last_name varchar(15) null)-- dml, populate sample datainsert into #catvalues ('Black123','ghost','','bigger')insert into #catvalues ('Arab0123','Hama','','Abbas')insert into #catvalues ('Mixed001','',null,null)insert into #catvalues ('Mixed002',null,null,null)insert into #catvalues ('Mixed003',null,'','Smith')insert into #catvalues ('White123','','','Talley')insert into #catvalues ('Yello123','Nick','H','Pisa')-- dml, name concatenation, get all or anyselect (first_name + ' ' + middle_name + ' ' + last_name) as namefrom #cat-- the above does not meet with requirement-- option 1select (IsNull(first_name,'') + ' ' + Case Len(middle_name) when 0 then'' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')) asnamefrom #cat-- option 2select (IsNull(first_name,'') + ' ' +IsNull(NullIf(Coalesce((middle_name + ' '),''),''),'') +IsNull(last_name,'')) as namefrom #catq:both option 1 and option 2 produces same result, which one is moredesirable?TIA.
View 4 Replies View RelatedI have a table with the next structure:
DECLARE @MaxCountHistogram TABLE
(
MaxId INT IDENTITY PRIMARY KEY NOT NULL,
PublicationId INT NOT NULL,
ProviderId INT NOT NULL,
DateLog DATETIME NOT NULL,
Amount FLOAT NOT NULL
)
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId
20150530 10.2564 3 432
20150630 3 Result of (13.2564- 10.2564 ) 3 432
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind
GO
/*STEP 2, #1*/
/* does it exist, if so drop it */
if exist (select * from dbo.sysobjects
where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[OrdersToSpain]
GO
/* Create the View */
create view "OrdersToSpain" AS
SELECT
Orders.OrderID AS Order_ID,
Orders.CustomerID AS Customer_ID,
Orders.OrderDate AS Ordered_Date.
Orders.ShippedDate AS Shipped_Date,
Orders.ShipCountry AS DestinationSpain
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipCounty LIKE '%SPAIN%'
GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7
Line 7: Incorrect syntax near '.'.
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
Thanks.
We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:
if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go
-- Create temp tables for sample data
create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)
-- Insert some sample data
insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')
-- Use lead function to determine how many months are between
-- consecutive dates per account
; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval
where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.
-- Show the results
select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis
Results (this is not getting me what I'm looking for):
RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122
[code]....
This is what I'm trying to achieve:
RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114
[code]....
The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.
I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.
I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
[code]....
is it possible to change the settings on this tool. i want to have it so that it does not time out at all? or is that possible??
View 1 Replies View RelatedHi all.I'm looking for a tool which should act like some kind of middleware/logical layer bewtween the SQL server and the webbased user interface.- It should be possible to easily create simple web forms (only datainput and output) without programming effort by "clicking" the fieldsand their order on the web mask within an admnistrative interface.- It should also be bossible to add "new fields" to the database,including field type, permissions, value lists etc. (excluding anybusiness logic) without programming effort by administration.I know, that most workflow tools or "Trouble Ticket Tools", based ondatabases have this feature to easily configure new masks and add newfields to the database, but i need it as an tool-unspecific layer forthe MS SQL Server.Thank you very much for your feedback, any ideas are welcome!Best regards, Heiko.
View 1 Replies View RelatedI couldn't find a "sql trace" utility like in sql 6.5... Does anybody know how/if I can do this?
View 2 Replies View Relatedif i dont use the trace to record all the sql transaction. and someone execute the delete command to delete one of the table (ofcourse the person have high enough permissions to do that). is there anyway i can find out who that user's ID is to run the deleted ?? Thanks:D :p
View 2 Replies View RelatedHi,
I'm trying to debug a vendor package and would like to turn on JDBC trace (either client or server side). The only information I found is DBCC TRACE which seems not very useful (I even don't know where the trace result located). Any help is appreciated!
Env: Windows Server 2003 & SQL Server 2000 8.00.818.
Jeff Li
Guys,
How to trace/find out some one has dropped a database from my QA environment? Unfortunately we havent enabled the trace on this server.
We havent find any useful information from SQL Server logs also.
Can any one reply me how to find the details of who dropped the DB, when? is there any query/SP/command/tools avaialble?
Regards
BSR
With Regards
BSR
hi all..
i have 2 tbl..
-- Prepare sample data
DECLARE@tbl1 TABLE (box varchar(10), loc varchar(5) )
INSERT@tbl1
SELECT'P1', 'aa' UNION ALL
SELECT'P1', 'bb' UNION ALL
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'
DECLARE@tbl2 TABLE (box varchar(10), loc varchar(5) )
INSERT@tbl2
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'
--expected result
SELECT 'P1' as box, 'aa' as Location, 'aa' as HeaderLoc UNION ALL
SELECT 'P1' as box, 'bb' as Location, 'aa' as HeaderLoc
how do i trace from @tbl1, that has 1 Box with more than 1 Location?
in this case P1 has 2 distinct location (aa & bb).. and left join in the loc from @tbl2 just to retrieve the loc for that box..
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
How do you set up the trace in SQL?
thanks
sorry, I am an Oracle guy trying to learn SQL
Hello:I am working with SQL Server 2000. I have a stored procedure that creates 3temporary tables (#temp1, #temp2, #temp3). When I view the trace of thestored procedure I see entries that have Object Created #temp1, ObjectCreated #temp2, Object Created #temp3. How can I fix the trace so it doesnot report any objects that get created in the tempdb.Any help will be appreciated.Alee
View 1 Replies View RelatedWhen we enable tracing for a web application where exactly the file or content of the file "Trace.axd" saves?
View 4 Replies View RelatedHi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.
Hi ,
I want to trace the deadlock information. I am enabling trace flags 1204 ,1205. Is there any difference in setting these trace flags by using DBCC
TRACEON and by setting them at command prompt by statrting the SQL server with SQLSERVR command. Actually i don't want to bring the server down. I want the information to be logged to Error log. Any help is greatly appreciated.
Hi There,
Is there any way to identify how many times a table is accessed by different users in a day from a database.
Thanks,
Madhu.
Hi all,
I was wondering how much of an overhead would Running this
trace (1204) have over the system. Will my perfomance
degrade significantly. If yes, by what percentage ???
The trace is 1204 to keep a watch on DeadLocking
I am running this command
C:mssql7innsqlservr -T1204 /dc:mssql7datamaster.mdf
How much performance degrade are we talking about here ?
The Application is Peoplsoft and the db sizes is about 10
Gb !!
Regards
Girish
SUPPOSE A THIRD PERSON WHO (MODIFIES THE STRUCTURE OF A TABLE) OR (DOING SOME MODIFICATIONS ON A CERTAIN COLUMN), HOW THE SQL SERVER DBA WILL FIND WHO HAS DONE THIS. WHETHER THESE MODIFICATIONS WILL BE STORED IN SYSTEMS TABLE?. THIS IS MY QUESTION. CAN ANYBODY GIVE SOLUTION TO THIS PLEASE?. THANKS ---- Srinivasan.
View 2 Replies View RelatedIf I issue a DBCC TRACEON (1205) from a query window from a client machine.. Does it continue to run when I close that connection (spid).
Is there a way for me to tell what traceflags are currently on?
Thanks,
Dano
Hello,
I have to create a trace to monitor how the stored proc.are performing.
The sp are for update/select/insert.
What all counters should I monitor?
The sp are called in from Java applications thru weblogic server and some of them are taking as long as 30 secs to fetch 80 records!
Any help/thoughts appreciated.
To solve a problem I encountered with Restoring from Backups in 6.5, I had to install a hotfix and thereafter do the load using Trace Flag 3282.I need help on using the trace flag (syntax etc.) Also there is no mention in books online of this particular Trace Flag.
Please help.
One of my SQL servers (v6.5 with SP5a) has begun to display 'DBCC TRACEON 208' in the Error Log. I have not been able to find any reference to the Trace Flag - 208. Can someone please help me?
View 2 Replies View RelatedHi All,
I've just joined a project for improving performance issue on MS-SqlServer(6.5).
I was DBA Sybase a couple years ago I am unsure that MSSQL works in a manner as
Sybase. I want to change some configuration options (sp_compile) and modify
the table and indexes definition for improving the performance and avoid having
deadlocks.
Is true that :
When field, which has a default value assigned to NULL, is equal
at a variable-length field and when you execute an update on it you will
get 2 transactions : first a delete and then insert ?
(On Sybase there is the concept of Index Covering to avoid that overhead
what about Ms-Sqlserver 6.5).
To avoid having deadlock I want to use the FILLFACTOR I think this option
is usefull only on cluster indexes, that's right ?
For huge tables the order of fields is important :
first position put the primary key field
after put the FK, secondary key, short type field (tinyint,int,datetime...)
go on with the alpha fields (char) NOT NULL
at last the NULL fields
Is that design efficient for Ms-SQLserver ?
Many thanks,
Herve
Hi folks
Is there any (easy!) way in which I can see the SQL that has been executed. I'm using stored procs that create & execute other stored procs(via sp_executesql). At the early stages there are often trivial errors in the created procs that cause rather general exception messages that do not give much of a clue as to where the error is. (It's tedious to find these in the debugger) ... and was wondering if there's any trace output type option I can turn on to see what sql has been presented for execution.
Thanks.
I ran a trace on my SQL Server and found that a single SELECT statement showed up the same number of times as the number of id numbers I was selecting on. In the hopes of optimizing my program, it ran worse :(
Instead of having:
SELECT * FROM table_name WHERE id='1'
SELECT * FROM table_name WHERE id='2'
SELECT * FROM table_name WHERE id='3'
...
SELECT * FROM table_name WHERE id='250'
for all the rows i need data from (sometimes up to 250 rows). I changed the select statement to:
SELECT * FROM table_name WHERE id='1' OR id='2' OR id='3' .. id='250'
When i run a SQL Trace if i have 250 id='num's in my SELECT, the same query shows up 250 times in the trace with high CPU and READS. Can Anyone explain and help me get the optimization Im looking for?