Hoe to Find Duplicate values?

I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert

Ho to filter dimension in BIDS

Hi,
I have 2 cubes, Cube_A and Cube_B, and a common dimension, Dim_C.
When I browse Cube_A I don't want to see dimension members that have some values (for example some name members that begin with caracter "A" or "Z").

How can I do this using calculation tab of "Business Intelligence Development Studion" (BIDS) ?

(I don't want to define role on dimension)
thanks.

We often limit sets on the axes as part of the query (MDX) statement, but I've never heard of making specific members of a set non-visible for all users and all browsers like this. I realize you said you don't want to create a role, but that may be the only way to accomplish this.

If someone else out there knows a way to pull this off, I'd be interested in hearing the solution.

Good luck,
Bryan

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbId FileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
--
Neil HamblyNeil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
--
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hamblysql

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbId FileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
Neil HamblyNeil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:

> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 9
40
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168
-
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbIdFileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
11 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
12 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
21 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
22 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
31 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
32 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
41 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
42 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
51 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
52 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
53 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
61 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
62 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
63 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
64 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
65 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
66 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
181 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
182 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
Neil Hambly
Neil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly
|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:

> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbIdFileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 11 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 12 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 21 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 22 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 31 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 32 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 41 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 42 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 51 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 52 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 53 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 61 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 62 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 63 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 64 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 65 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 66 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 181 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 182 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly

High Disk queue length.. how slow

I know if you see high disk queue length , it says you may have an IO
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
Thanks
Hi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and you
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:

> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>
|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.
|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_shea/archive/2007/11/12/disk-queue-length-some-data-points-may-help.aspx for more info.
Linchi
"Hassan" wrote:

> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>

High Disk queue length.. how slow

I know if you see high disk queue length , it says you may have an IO
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
ThanksHi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archiv...ed
.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and yo
u
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:

> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_she...s-may-help.aspx for more info.
Linchi
"Hassan" wrote:

> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>

High Disk queue length.. how slow

I know if you see high disk queue length , it says you may have an IO
bottleneck..
So the question is when i see values like 10 or 20 or 40 for my disk queue
length, what do the values actually indicate ? What is the unit ? How slow
does a query with say a value of 40 correlate to vs a query with queue
length of 10 ?
ThanksHi Hassan
A long queue length may be an indication of an issue but not always, see
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
as SQL Server may push the queue length above an average of 2 per disc in
certain circumstances. If these are peaks then it is less likely to be an
issue than a sustained value. Perfmon has several disc queue counters and you
can differentiate average, current, read, write and total lengths. Each
counter is the number of requests in the queue.
You may want to load this information into a SQL Profile and look at what
the system is doing while the queue are at these values.
John
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>|||> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
To expand on John's response, I often monitor both disk queue length and
checkpoint pages/sec and ignore the queue length during checkpoint spikes.
Also, even with a disk bottleneck, you will not necessarily see queuing.
You need to know how much activity your disk subsystem can handle (e.g.
transfers/sec.) so that you can know when it is saturated. One approach is
to first establish a baseline under ideal conditions using a tool like
SQLIOSim (http://support.microsoft.com/kb/231619). You'll know you have a
bottleneck when your activity approaches the baseline performance metrics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||Hassan, I see you have several posts dealing with performance related
issues. Take a class and/or hire a professional to mentor you/review your
systems. Both will be money well spent - especially the latter if you get
someone good.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:e3OjbuCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I know if you see high disk queue length , it says you may have an IO
>bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>|||On Sun, 11 Nov 2007 07:57:46 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>> So the question is when i see values like 10 or 20 or 40 for my disk queue
>> length, what do the values actually indicate ? What is the unit ? How slow
>> does a query with say a value of 40 correlate to vs a query with queue
>> length of 10 ?
>To expand on John's response, I often monitor both disk queue length and
>checkpoint pages/sec and ignore the queue length during checkpoint spikes.
>Also, even with a disk bottleneck, you will not necessarily see queuing.
Good advice.
The thing about the disk queue is that once that's an issue, it trumps
pretty much any other performance issue. So it's hard to answer the
question about how slow. Compared to no disk queue at all, it's
probably a big, huge difference, 10x, 100x, who knows, but with big
data you are simply going to have to do that IO sometime, but that
should still be possible with a small queue.
J.|||Disk queue length alone is not a reliable indicator of a bottleneck in the
disk subsystem. To detect disk I/O bottleneck, it's better to look at I/O
latency (e.g. Avg disk sec/read and Avg disk sec/write) plus I/O block sizes
(e.g. Bytes/read and bytes/write), look at these metrics in combination with
disk queue length.
Also, see
http://sqlblog.com/blogs/linchi_shea/archive/2007/11/12/disk-queue-length-some-data-points-may-help.aspx for more info.
Linchi
"Hassan" wrote:
> I know if you see high disk queue length , it says you may have an IO
> bottleneck..
> So the question is when i see values like 10 or 20 or 40 for my disk queue
> length, what do the values actually indicate ? What is the unit ? How slow
> does a query with say a value of 40 correlate to vs a query with queue
> length of 10 ?
> Thanks
>
>

High CPU value in Profiler

We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.
Hassan, do you have Books Online? You should. From there:
"In SQL Server 2005, the server reports the duration of an event in
microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
time used by the event in milliseconds (one thousandth, or 10^-3, of a
second). In SQL Server 2000, the server reported both duration and CPU time
in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user
interface displays the Duration column in milliseconds by default, but when
a trace is saved to either a file or a database table, the Duration column
value is written in microseconds."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>
|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "In SQL Server 2005, the server reports the duration of an event in
> microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
> time used by the event in milliseconds (one thousandth, or 10^-3, of a
> second). In SQL Server 2000, the server reported both duration and CPU
> time in milliseconds. In SQL Server 2005, the SQL Server Profiler
> graphical user interface displays the Duration column in milliseconds by
> default, but when a trace is saved to either a file or a database table,
> the Duration column value is written in microseconds."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>
|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:[vbcol=seagreen]
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...

High CPU value in Profiler

We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"In SQL Server 2005, the server reports the duration of an event in
microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
time used by the event in milliseconds (one thousandth, or 10^-3, of a
second). In SQL Server 2000, the server reported both duration and CPU time
in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user
interface displays the Duration column in milliseconds by default, but when
a trace is saved to either a file or a database table, the Duration column
value is written in microseconds."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "In SQL Server 2005, the server reports the duration of an event in
> microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
> time used by the event in milliseconds (one thousandth, or 10^-3, of a
> second). In SQL Server 2000, the server reported both duration and CPU
> time in milliseconds. In SQL Server 2005, the SQL Server Profiler
> graphical user interface displays the Duration column in milliseconds by
> default, but when a trace is saved to either a file or a database table,
> the Duration column value is written in microseconds."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:[vbcol=seagreen]
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CP
U
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in me
ssage
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...

High CPU value in Profiler

We see CPU values of 20000 + in Profiler for some statements ? Whats the
unit for it ? Is it in ms(milliseconds) ?
If its in milliseconds, what does that mean? Can a high duration of CPU
means higher processing power or just longer time to process ? Please help
me understand.Hassan, do you have Books Online? You should. From there:
"In SQL Server 2005, the server reports the duration of an event in
microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
time used by the event in milliseconds (one thousandth, or 10^-3, of a
second). In SQL Server 2000, the server reported both duration and CPU time
in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user
interface displays the Duration column in milliseconds by default, but when
a trace is saved to either a file or a database table, the Duration column
value is written in microseconds."
A
"Hassan" <hassan@.hotmail.com> wrote in message
news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> unit for it ? Is it in ms(milliseconds) ?
> If its in milliseconds, what does that mean? Can a high duration of CPU
> means higher processing power or just longer time to process ? Please help
> me understand.
>|||Thanks Aaron.
What would 20000 ms mean from a CPU perspective ?
Is that considered a high CPU or is just running for 20 secs using some CPU
cycles ?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hassan, do you have Books Online? You should. From there:
> "In SQL Server 2005, the server reports the duration of an event in
> microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
> time used by the event in milliseconds (one thousandth, or 10^-3, of a
> second). In SQL Server 2000, the server reported both duration and CPU
> time in milliseconds. In SQL Server 2005, the SQL Server Profiler
> graphical user interface displays the Duration column in milliseconds by
> default, but when a trace is saved to either a file or a database table,
> the Duration column value is written in microseconds."
> A
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>|||Hassan
Run those statements and specify SET STATISTICS TIME ON
"Hassan" <hassan@.hotmail.com> wrote in message
news:eOjtAHAyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some
> CPU cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hassan, do you have Books Online? You should. From there:
>> "In SQL Server 2005, the server reports the duration of an event in
>> microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
>> time used by the event in milliseconds (one thousandth, or 10^-3, of a
>> second). In SQL Server 2000, the server reported both duration and CPU
>> time in milliseconds. In SQL Server 2005, the SQL Server Profiler
>> graphical user interface displays the Duration column in milliseconds by
>> default, but when a trace is saved to either a file or a database table,
>> the Duration column value is written in microseconds."
>> A
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
>> We see CPU values of 20000 + in Profiler for some statements ? Whats the
>> unit for it ? Is it in ms(milliseconds) ?
>> If its in milliseconds, what does that mean? Can a high duration of CPU
>> means higher processing power or just longer time to process ? Please
>> help me understand.
>>
>|||It means that 20 seconds worth of CPU cycles were used. So that could be
20 seconds of one CPU (or core), 10 seconds for 2 CPU's, etc. So if the
system only has one CPU (with one core), and the elapsed time was also
20 seconds, then this query has saturated the CPU for 20 seconds. If the
running time was 40 seconds (and still assuming 1 CPU), then the CPU was
used for (on average) 50% for this query.
HTH,
Gert-Jan
Hassan wrote:
> Thanks Aaron.
> What would 20000 ms mean from a CPU perspective ?
> Is that considered a high CPU or is just running for 20 secs using some CPU
> cycles ?
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eHYbw5%23xHHA.5980@.TK2MSFTNGP04.phx.gbl...
> > Hassan, do you have Books Online? You should. From there:
> >
> > "In SQL Server 2005, the server reports the duration of an event in
> > microseconds (one millionth, or 10^-6, of a second) and the amount of CPU
> > time used by the event in milliseconds (one thousandth, or 10^-3, of a
> > second). In SQL Server 2000, the server reported both duration and CPU
> > time in milliseconds. In SQL Server 2005, the SQL Server Profiler
> > graphical user interface displays the Duration column in milliseconds by
> > default, but when a trace is saved to either a file or a database table,
> > the Duration column value is written in microseconds."
> >
> > A
> >
> >
> > "Hassan" <hassan@.hotmail.com> wrote in message
> > news:u$r6qx%23xHHA.5584@.TK2MSFTNGP02.phx.gbl...
> >> We see CPU values of 20000 + in Profiler for some statements ? Whats the
> >> unit for it ? Is it in ms(milliseconds) ?
> >>
> >> If its in milliseconds, what does that mean? Can a high duration of CPU
> >> means higher processing power or just longer time to process ? Please
> >> help me understand.
> >>
> >
> >

Hiding Report Parameters ToolBar

Hi,
I am generating report using stored procedures. I am passing values to the
SP parameters for the report through URL. When I generate the report, the
parameters are being displayed in a toolbar at the top of the report. Is
there any way I can hide the parameter toolbox or parameter list while
generating the report.
Regards,
Sudhakara.T.P.Have you set the Report Parameters to Hidden?
daw
"Sudhakara.T.P." wrote:
> Hi,
> I am generating report using stored procedures. I am passing values to the
> SP parameters for the report through URL. When I generate the report, the
> parameters are being displayed in a toolbar at the top of the report. Is
> there any way I can hide the parameter toolbox or parameter list while
> generating the report.
> Regards,
> Sudhakara.T.P.|||Hi,
I just experienced the same issue for my reports and I found out there
is plenty of ways to work it out. Share with you my findings. Many thanks to
SSRS experts!!
1) Create a custom CSS file at \MSSQL\Reporting
Services\ReportServer\Styles\. Modify the .ToolbarRefresh or
.ToolbarParameters etc. Change display:inline to display:none. This is to
hide certain items/icons at Toolbar which is not frequently used. Then, set
your access url like :
http://server/reportserver?/reportname&rc:Stylesheet=MyStyle (without the
.css extension)
More reference at
http://blogs.msdn.com/bimusings/archive/2005/07/08/436887.aspx
2) Add &rc:Toolbar=false after your report url to hide the entire toolbar.
3) Add &rc:Parameters=false / &rc:Parameters=Collapsed after your report
url to hide parameters list only.
4) Clear the checkbox of Prompt User through Report
Manager>Properties>Parameters.
HTH.
regards,
sammy
"daw" wrote:
> Have you set the Report Parameters to Hidden?
> daw
> "Sudhakara.T.P." wrote:
> > Hi,
> > I am generating report using stored procedures. I am passing values to the
> > SP parameters for the report through URL. When I generate the report, the
> > parameters are being displayed in a toolbar at the top of the report. Is
> > there any way I can hide the parameter toolbox or parameter list while
> > generating the report.
> >
> > Regards,
> > Sudhakara.T.P.

Hiding parameters

I have a report that has to show values in the Report Header section. This is
solved by creating parameters that gets the values from a dataset.
But I can't hide these parameters from the user. If I leave the promt field
empty it will become read only, but not hidden.
Is there a way of hiding these parameters?I beleive if you install SP1 for Reporting Services you can hide the header
(by not providing the prompt) but still be able to set the values...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"karl" <karl@.discussions.microsoft.com> wrote in message
news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
>I have a report that has to show values in the Report Header section. This
>is
> solved by creating parameters that gets the values from a dataset.
> But I can't hide these parameters from the user. If I leave the promt
> field
> empty it will become read only, but not hidden.
> Is there a way of hiding these parameters?|||Thanks.
But SP1 is already installed.
"Wayne Snyder" wrote:
> I beleive if you install SP1 for Reporting Services you can hide the header
> (by not providing the prompt) but still be able to set the values...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "karl" <karl@.discussions.microsoft.com> wrote in message
> news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> >I have a report that has to show values in the Report Header section. This
> >is
> > solved by creating parameters that gets the values from a dataset.
> >
> > But I can't hide these parameters from the user. If I leave the promt
> > field
> > empty it will become read only, but not hidden.
> >
> > Is there a way of hiding these parameters?
>
>|||Karl - did you set the prompt to blank via the report manager or the
designer? You have to do it via the designer. I don't trust using the report
manager to set properties.
If you do it via the report designer - just leave the prompt blank. I have
had great success using this method.
=-Chris
"karl" <karl@.discussions.microsoft.com> wrote in message
news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
>I have a report that has to show values in the Report Header section. This
>is
> solved by creating parameters that gets the values from a dataset.
> But I can't hide these parameters from the user. If I leave the promt
> field
> empty it will become read only, but not hidden.
> Is there a way of hiding these parameters?|||Thanks.
This is done in the Report Designer.
But since I now know that this should work, I'll just look around in my
solution to see if something is done wrong.
Thanks again.
Karl
"Christopher Conner" wrote:
> Karl - did you set the prompt to blank via the report manager or the
> designer? You have to do it via the designer. I don't trust using the report
> manager to set properties.
> If you do it via the report designer - just leave the prompt blank. I have
> had great success using this method.
> =-Chris
> "karl" <karl@.discussions.microsoft.com> wrote in message
> news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> >I have a report that has to show values in the Report Header section. This
> >is
> > solved by creating parameters that gets the values from a dataset.
> >
> > But I can't hide these parameters from the user. If I leave the promt
> > field
> > empty it will become read only, but not hidden.
> >
> > Is there a way of hiding these parameters?
>
>|||By the way...
I have the Report Server and SQL Server on two different servers.
Currently the SP1 is only installed on the Report Server (server).
Do I also need to install the SP1 on the server where SQL Server is located?
Karl
"karl" wrote:
> Thanks.
> This is done in the Report Designer.
> But since I now know that this should work, I'll just look around in my
> solution to see if something is done wrong.
> Thanks again.
>
> Karl
> "Christopher Conner" wrote:
> > Karl - did you set the prompt to blank via the report manager or the
> > designer? You have to do it via the designer. I don't trust using the report
> > manager to set properties.
> >
> > If you do it via the report designer - just leave the prompt blank. I have
> > had great success using this method.
> >
> > =-Chris
> >
> > "karl" <karl@.discussions.microsoft.com> wrote in message
> > news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> > >I have a report that has to show values in the Report Header section. This
> > >is
> > > solved by creating parameters that gets the values from a dataset.
> > >
> > > But I can't hide these parameters from the user. If I leave the promt
> > > field
> > > empty it will become read only, but not hidden.
> > >
> > > Is there a way of hiding these parameters?
> >
> >
> >|||The Report Server says Version 8.00.878.00.
And if I'm not wrong this is the correct number for SP1.
"karl" wrote:
> By the way...
> I have the Report Server and SQL Server on two different servers.
> Currently the SP1 is only installed on the Report Server (server).
> Do I also need to install the SP1 on the server where SQL Server is located?
>
> Karl
>
> "karl" wrote:
> > Thanks.
> >
> > This is done in the Report Designer.
> > But since I now know that this should work, I'll just look around in my
> > solution to see if something is done wrong.
> >
> > Thanks again.
> >
> >
> > Karl
> >
> > "Christopher Conner" wrote:
> >
> > > Karl - did you set the prompt to blank via the report manager or the
> > > designer? You have to do it via the designer. I don't trust using the report
> > > manager to set properties.
> > >
> > > If you do it via the report designer - just leave the prompt blank. I have
> > > had great success using this method.
> > >
> > > =-Chris
> > >
> > > "karl" <karl@.discussions.microsoft.com> wrote in message
> > > news:CD65FAC0-1860-4967-9333-3F894C8E23BF@.microsoft.com...
> > > >I have a report that has to show values in the Report Header section. This
> > > >is
> > > > solved by creating parameters that gets the values from a dataset.
> > > >
> > > > But I can't hide these parameters from the user. If I leave the promt
> > > > field
> > > > empty it will become read only, but not hidden.
> > > >
> > > > Is there a way of hiding these parameters?
> > >
> > >
> > >

hiding parameters

I keep seeing post saying that you can hide parameters and pass the values
through a querystring by clearing out the prompt string and unchecking prompt
user. However, when I try this i get an error saying the propery is read
only. Any Suggestions.
--
Thanks,
Danny BaderI too get this error if I leave the parameter prompt blank. I keep hearing
that you have to install SP1 to make this go away. I have SP2 installed and
it still happens. Does SP2 not have the SP1 fixes in it also? If not, is it
okay to install SP1 even though I already have SP2 installed?
Robert|||I have SP2 installed as well.
--
Thanks,
Danny Bader
"Robert RVRK" wrote:
> I too get this error if I leave the parameter prompt blank. I keep hearing
> that you have to install SP1 to make this go away. I have SP2 installed and
> it still happens. Does SP2 not have the SP1 fixes in it also? If not, is it
> okay to install SP1 even though I already have SP2 installed?
> Robert|||Hi Danny,
I think I might have found the solution. Instead of blanking out the
parameter prompt, enter a space for the parameter prompt. Supposedly this
will work when you view the report in the report viewer (but it won't in the
IDE, so don't give up until you've tried it in the viewer). I am going to go
try myself now...
Robert|||> through a querystring by clearing out the prompt string and unchecking prompt
> user.
Nope. Just clear the prompt string and DON'T uncheck the prompt user option.
--
Please mark the correct/helpful answers!|||you can do by following steps:
1. check "allow null"
2. Clear the prompt string
3. select "none" for default value
4. in report manager, check "prompt user", but clear the prompt string
so, this parameter will not appear in parameter panel
and you can pass value to this parameter through URL
"Danny Bader" wrote:
> I keep seeing post saying that you can hide parameters and pass the values
> through a querystring by clearing out the prompt string and unchecking prompt
> user. However, when I try this i get an error saying the propery is read
> only. Any Suggestions.
> --
> Thanks,
> Danny Bader

Hiding NULL

In SQL 2000 Query Analyzer, you can set up the text output pane to
leave null values blank.

That could give you this (assuming it's set to comma-delimited) --

CREATE TABLE #Test (A int, B int)
INSERT #Test SELECT 1,2
INSERT #Test SELECT NULL,4
INSERT #Test SELECT 5,NULL
INSERT #Test SELECT NULL,NULL
SELECT * FROM #Test

A,B
1,2
,4
5,
,

I can't figure out how to set up 2005 Management Studio the same way.
I can only manage to get outputs like this, with "NULL" instead of
empty fields --

A,B
1,2
NULL,4
5,NULL
NULL,NULL

How can I get rid of the NULLs?

Thanks,
Jim(jim_geissman@.countrywide.com) writes:
> I can't figure out how to set up 2005 Management Studio the same way.
> I can only manage to get outputs like this, with "NULL" instead of
> empty fields --
> A,B
> 1,2
> NULL,4
> 5,NULL
> NULL,NULL
> How can I get rid of the NULLs?

All I can suggest is that you go to
http://lab.msdn.microsoft.com/ProductFeedback/ and submit a suggestion
that Microsoft restores this possibility in the next version of SQL
Server.

There are many features in QA that did not make it to Mgmt Studio. To be
fair, QA has some odd quirks, even if several of them are practical. For
instance, this one is good if you want to copy and paste into Excel. Then
again, in Mgmt Studio, it's easier to copy and paste from grid, as you
now can opt to include the headers. (Tools->Options->Query Results->Results
to Grid.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Erland. I will visit MS and make that request.

Jim

Hiding fields in Fact-Dimension Table

I have a fact table i set as both dimension and fact. so this one is linked to different dimension tables and at the same time, some of its values are in measure group.

the thing is, in the list of its attributes, i can see the keys to which the different dimensions are linked. i cannot remove these key because they serve as links. but i dont want the users to see this key. it's not helpful to them anyway. how can i do it?

Example:

Fct_ServiceJobItem (Set as both dimension and fact)

JobNo

LineNum

Partkey

Amount

Dim_Parts (Dimension)

PartKey

PartNo

.

.

I dont want to see the Partkey in the Dimension Fct_ServiceJobItem. But this is linked to Dim_Parts and removing it can cause error.

cherriesh

In the designer , highlight the Fact, or attribute you want to hide and press F4.

set Visible for facts, or attributehierarchyvisible for dimensions to false

Hiding a subreport

We have a parameter that takes the values of "Yes" or "No" (non-queried). If the user chooses "Yes", we want the subreport to display, but remain hidden on "No".

In the expression for visibility, we have the following:

=IIf(cstr(Parameters!Heading.Value = "Yes"),False,True)

When the report runs we get the following error: "The input string was not in the correct format."

Has anybody seen this before?

Thanks for the information.

Problem solved - the parameter data-type was set to boolean.

Hide/Show parameter based on Multi-Valued Parameter Selection

Hi!

I have a parameter that sould be visible only if certains values are selected on a
multi-value parameter. I tried to use an expression for the parameter's hidden property, but I saw it's not possible (at least not in the usual way, as with tables). Does anyone know how to do it?

Thanks!

There is no way to do this through RDL, but if you are using the report viewer controls, you can programmatically hide the parameters using the ReportViewer control API.