sql - How can I improve this query? Trying to figure out where to do indexing -

i've been given task improve below query because it's kinda slow.

select 9 fieldcount, a.accountname field1, u.fullname field2, a.city field3, a.state field4, a.zip5 field5, count(distinct c.contact_id) field6, count(distinct l.lead_id) field7, a.account_id field8, a.createdatetime field9, row_number() on (order a.createdatetime desc) rownum  dynamic_account left join dynamic_contact c on a.account_id = c.account_id left join dynamic_lead l on a.account_id = l.account_id, static_list ls, static_list lc, static_user u (a.account_id > 0) , (a.source_id = ls.list_id) , (a.category_id = lc.list_id) , (a.accountsr01_id = u.user_id)  group accountname, lc.title, u.fullname, a.city, a.state, a.zip5, a.account_id, a.createdatetime 

can please give me pointer of how joint can improved or put index? thanks

edit: below create scripts of tables generated sql management studio. dynamic_account:

create table [dbo].[dynamic_account]( [account_id] [int] identity(1,1) not null, [recordstatus] [int] null, [attributelist] [nvarchar](max) null, [createuser_id] [int] null, [createdatetime] [datetime] null, [changeuser_id] [int] null, [changedatetime] [datetime] null, [viewuser_id] [int] null, [viewdatetime] [datetime] null, [inactiveflag] [bit] not null, [source_id] [int] null, [campaign_id] [int] null, [comments] [nvarchar](max) null, [parent_id] [int] null, [subsidiary_id] [int] null, [individualflag] [bit] not null, [accounttype] [nvarchar](50) null, [accountstage] [nvarchar](50) null, [accountname] [nvarchar](100) null, [companyname] [nvarchar](100) null, [address1] [nvarchar](100) null, [address2] [nvarchar](100) null, [address3] [nvarchar](100) null, [address4] [nvarchar](100) null, [city] [nvarchar](100) null, [state] [nvarchar](100) null, [postalcode] [nvarchar](20) null, [zip5] [nvarchar](5) null, [zip4] [nvarchar](4) null, [country] [nvarchar](50) null, [donotmailflag] [bit] not null, [territorylist] [nvarchar](255) null, [phonenumber] [nvarchar](30) null, [donotcallflag] [bit] not null, [faxnumber] [nvarchar](30) null, [category_id] [int] null, [othernamelist] [nvarchar](255) null, [website] [nvarchar](255) null, [geolocation] [nvarchar](100) null, [overrideterritoryflag] [bit] not null, [accountsr01_id] [int] null, [accountsr02_id] [int] null, [accountsr03_id] [int] null, [accountsr04_id] [int] null, [accountsr05_id] [int] null, [accountsr06_id] [int] null, [accountsr07_id] [int] null, [accountsr08_id] [int] null, [accountsr09_id] [int] null, [accountsr10_id] [int] null, [accountsr11_id] [int] null, [accountsr12_id] [int] null, [accountsr13_id] [int] null, [accountsr14_id] [int] null, [accountsr15_id] [int] null, [annualrevenue] [nvarchar](100) null, [employeecount] [nvarchar](100) null, [businessduration] [nvarchar](100) null, [custom_01] [nvarchar](255) null, [custom_02] [nvarchar](255) null, [custom_03] [nvarchar](255) null, [custom_04] [nvarchar](255) null, [custom_05] [nvarchar](255) null, [custom_06] [nvarchar](255) null, [custom_07] [nvarchar](255) null, [custom_08] [nvarchar](255) null, [custom_09] [nvarchar](255) null, [custom_10] [nvarchar](255) null, [custom_11] [nvarchar](255) null, [custom_12] [nvarchar](255) null, [custom_13] [nvarchar](255) null, [custom_14] [nvarchar](255) null, [custom_15] [nvarchar](255) null, [custom_16] [nvarchar](255) null, [custom_17] [nvarchar](255) null, [custom_18] [nvarchar](255) null, [custom_19] [nvarchar](255) null, [custom_20] [nvarchar](255) null, constraint [pk_dynamic_account_1] primary key clustered  (     [account_id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off allow_row_locks = on, allow_page_locks = on) on [primary] ) 

dynamic_contact:

create table [dbo].[dynamic_contact]( [contact_id] [int] identity(1,1) not null, [recordstatus] [int] null, [attributelist] [nvarchar](max) null, [createuser_id] [int] null, [createdatetime] [datetime] null, [changeuser_id] [int] null, [changedatetime] [datetime] null, [viewuser_id] [int] null, [viewdatetime] [datetime] null, [inactiveflag] [bit] not null, [source_id] [int] null, [campaign_id] [int] null, [comments] [nvarchar](max) null, [subsidiary_id] [int] null, [account_id] [int] null, [contactname] [nvarchar](50) null, [salutation] [nvarchar](10) null, [firstname] [nvarchar](50) null, [middleinitial] [nvarchar](10) null, [lastname] [nvarchar](50) null, [department] [nvarchar](255) null, [jobtitle] [nvarchar](255) null, [rolelist] [nvarchar](255) null, [donotmailflag] [bit] not null, [workphonenumber] [nvarchar](30) null, [donotcallworkflag] [bit] not null, [cellphonenumber] [nvarchar](30) null, [donotcallcellflag] [bit] not null, [homephonenumber] [nvarchar](30) null, [donotcallhomeflag] [bit] not null, [emailaddress] [nvarchar](100) null, [donotemailflag] [bit] not null, [altemailaddress] [nvarchar](100) null, [donotemailaltflag] [bit] not null, [productinterestlist] [nvarchar](255) null, [custom_01] [nvarchar](255) null, [custom_02] [nvarchar](255) null, [custom_03] [nvarchar](255) null, [custom_04] [nvarchar](255) null, [custom_05] [nvarchar](255) null, [custom_06] [nvarchar](255) null, [custom_07] [nvarchar](255) null, [custom_08] [nvarchar](255) null, [custom_09] [nvarchar](255) null, [custom_10] [nvarchar](255) null, [custom_11] [nvarchar](255) null, [custom_12] [nvarchar](255) null, [productinterestlistold] [nvarchar](255) null,  constraint [pk_dynamic_contact] primary key clustered  (     [contact_id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off allow_row_locks = on, allow_page_locks = on) on [primary] ) 

dynamic_lead:

create table [dbo].[dynamic_lead]( [lead_id] [int] identity(1,1) not null, [recordstatus] [int] null, [attributelist] [nvarchar](max) null, [createuser_id] [int] null, [createdatetime] [datetime] null, [changeuser_id] [int] null, [changedatetime] [datetime] null, [viewuser_id] [int] null, [viewdatetime] [datetime] null, [source_id] [int] null, [campaign_id] [int] null, [comments] [nvarchar](max) null, [subsidiary_id] [int] null, [contact_id] [int] null, [account_id] [int] null, [leaddatetime] [datetime] null, [description] [nvarchar](255) null, [details] [nvarchar](max) null, [overrideterritoryflag] [bit] null, [salesrep_id] [int] null, [salesgroup_id] [int] null, [leadquality_id] [int] null, [leadstage_id] [int] null, [leadstatus_id] [int] null, [activitystatus_id] [int] null, [disqualifiedreason_id] [int] null, [productinterestlist] [nvarchar](255) null, [score] [int] null, [custom_01] [nvarchar](255) null, [custom_02] [nvarchar](255) null, [custom_03] [nvarchar](255) null, [custom_04] [nvarchar](255) null, [custom_05] [nvarchar](255) null, [custom_06] [nvarchar](255) null, [custom_07] [nvarchar](255) null, [custom_08] [nvarchar](255) null, [custom_09] [nvarchar](255) null, [custom_10] [nvarchar](255) null, [custom_11] [nvarchar](255) null, [custom_12] [nvarchar](255) null, [productinterestlistold] [nvarchar](255) null, constraint [pk_dynamic_lead] primary key clustered  (     [lead_id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off,     allow_row_locks = on, allow_page_locks = on) on [primary] ) 

static_list:

create table [dbo].[static_list]( [list_id] [int] identity(1,1) not null, [recordstatus] [int] null, [attributelist] [ntext] null, [createuser_id] [int] null, [createdatetime] [datetime] null, [changeuser_id] [int] null, [changedatetime] [datetime] null, [inactiveflag] [bit] null, [listname] [nvarchar](100) null, [title] [nvarchar](100) null, [parent_id] [int] null, [displayindex] [int] null, [defaultflag] [bit] null, [external_id] [nvarchar](255) null, [custom_01] [nvarchar](255) null, [custom_02] [nvarchar](255) null, [custom_03] [nvarchar](255) null, [custom_04] [nvarchar](255) null, [custom_05] [nvarchar](255) null, [custom_06] [nvarchar](255) null, [custom_07] [nvarchar](255) null, [custom_08] [nvarchar](255) null, [custom_09] [nvarchar](255) null, [custom_10] [nvarchar](255) null, [custom_11] [nvarchar](255) null, [custom_12] [nvarchar](255) null, [custom_13] [nvarchar](255) null, [custom_14] [nvarchar](255) null, [custom_15] [nvarchar](255) null, [custom_16] [nvarchar](255) null, [custom_17] [nvarchar](255) null, [custom_18] [nvarchar](255) null, [custom_19] [nvarchar](255) null, [custom_20] [nvarchar](255) null, constraint [pk_tmp_static_list] primary key clustered  (     [list_id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) 

static_user:

create table [dbo].[static_user]( [user_id] [int] identity(1,1) not null, [recordstatus] [int] null, [attributelist] [nvarchar](max) null, [createuser_id] [int] null, [createdatetime] [datetime] null, [changeuser_id] [int] null, [changedatetime] [datetime] null, [inactiveflag] [bit] not null, [username] [nvarchar](100) null, [emailaddress] [nvarchar](150) null, [legalname] [nvarchar](100) null, [fullname] [nvarchar](100) null, [firstname] [nvarchar](100) null, [lastname] [nvarchar](100) null, [displayname] [nvarchar](100) null, [nickname] [nvarchar](100) null, [cellphonenumber] [nvarchar](100) null, [workphonenumber] [nvarchar](100) null, [faxnumber] [nvarchar](100) null, [jobtitle] [nvarchar](100) null, [department] [nvarchar](100) null, [officename] [nvarchar](100) null, [address1] [nvarchar](100) null, [address2] [nvarchar](100) null, [address3] [nvarchar](100) null, [city] [nvarchar](100) null, [state] [nvarchar](100) null, [zip] [nvarchar](100) null, [country] [nvarchar](100) null, [lastaccess] [datetime] null, [custom_01] [nvarchar](255) null, [custom_02] [nvarchar](255) null, [custom_03] [nvarchar](255) null, [custom_04] [nvarchar](255) null, [custom_05] [nvarchar](255) null, [custom_06] [nvarchar](255) null, [custom_07] [nvarchar](255) null, [custom_08] [nvarchar](255) null, [custom_09] [nvarchar](255) null, [custom_10] [nvarchar](255) null, [custom_11] [nvarchar](255) null, [custom_12] [nvarchar](255) null, constraint [pk_dbo_static_user] primary key clustered  (     [user_id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on 

row counts: dynamic_account: 180,000 rows

dynamic_contact: 310,000 rows

dynamic_lead: 130,000 rows

static_list: 1300 rows

static_user: 250 rows

i have no index on table.

since results not include static_list, removing static_list clause , using exists or in in clause help. also, should consistent how join tables. finally, since don't show static_list table, including in group clause doesn't make sense.

you might able add indices speed process, changing query follows have largest impact:

select 9 fieldcount, a.accountname field1, u.fullname field2, a.city field3, a.state field4, a.zip5 field5, count(distinct c.contact_id) field6, count(distinct l.lead_id) field7, a.account_id field8, a.createdatetime field9, row_number() on (order a.createdatetime desc) rownum  dynamic_account left join dynamic_contact c on a.account_id = c.account_id left join dynamic_lead l on a.account_id = l.account_id, inner join static_user u on a.accountsr01_id = u.user_id (a.account_id > 0) , exists(select 1 static_list a.category_id = list_id or a.source_id = list_id) group accountname, u.fullname, a.city, a.state, a.zip5, a.account_id, a.createdatetime